none
SQL Server agent job failed. error Code: 0xC0202009

    Question

  • i can run in VS. but when i want to run at SSIS job agent, it failed.

    below is my error message

    error Message
    Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  8:23:40 PM  Error: 2013-08-18 20:23:50.47     Code: 0xC0202009     Source: XYZ Connection manager "ABCPROD"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80070005.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80070005  Description: "Access is denied.".  End Error  Error: 2013-08-18 20:23:50.47     Code: 0xC020801C     Source: BONUS Source [97]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ABCPROD" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-08-18 20:23:50.47     Code: 0xC0047017     Source: BONUS SSIS.Pipeline     Description: BONUS Source failed validation and returned error code 0xC020801C.  End Error  Error: 2013-08-18 20:23:50.47     Code: 0xC004700C     Source: BONUS SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-08-18 20:23:50.47     Code: 0xC0024107     Source: BONUS      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  8:23:40 PM  Finished: 8:23:50 PM  Elapsed:  9.672 seconds.  The package execution failed.  The step failed.

    Please does anyone have any experience with a similar problem and if so, how did you get round it?

     

    Thank you...

    Sunday, August 18, 2013 12:51 PM

Answers

  • ya, run as SQL SERVER AGENT SERVICES ACCOUNT.

    Well make sure that the user behind that account has the appropiate rights or use a credential and proxy instead of the default SQL Server Agent Account.

    See step 3 at http://microsoft-ssis.blogspot.com/2012/09/who-is-running-package.html


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Monday, August 19, 2013 5:28 AM
  • Hi,

    From the error message, it seems that when you created your OLE DB connection manager to your source, you spesified "Use Windows Authentication" (see image below).  When you run the package through VS, it runs under your account.  Your account has access to the source system. 

    However, when you run the package through SQL Server Agent, it no longer runs as yourself.  From the message, I can see that it uses NT Service\SQLSERVERAGENT, which is the local SQL Server Agent service. 

    You have several options to resolve this.  Some of them are:

    1. Give the NT Service\SQLSERVERAGENT account access to the source.  However, because this account is local to the database server you will only be able to give it access to databases on that server.
    2. Create a domain service account and run SQL Server Agent using that account.  Then give that domain account access to the source.
    3. Create a SQL Server Agent proxy which will allow you to run the package through SQL Server Agent, but using your credentials.  The following link explains how to do this in detail. http://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

    Hope this was helpful.

    Regards,


    Marius  (Microsoft BI Solutions Architect)
    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 19, 2013 5:31 AM

All replies