none
not able to run SSIS package using SQL Agent job

    Question

  • Hi,

        I have created SSIS package which extracts data from Access database to SQL Server. I am able to run the job manually but when I try to schedule a job using SQL Server Agent , it is giving me below error :

    Executed as user: DOMAIN\ADMINISTRATOR. ... Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  5:53:50 PM  Error: 2008-09-26 17:53:52.19     Code: 0xC0202009     Source: ReportsImport Connection manager "SourceConnectionOLEDB"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "'K:\Access\Data\PROD\Data.mdb' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".  End Error  Error: 2008-09-26 17:53:52.20     Code: 0xC020801C     Source: Data Flow Task Source - Branch Master [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error .  The step failed.

        Would appreciate the reply.

    Thanks,
    Sam. 

    Monday, August 17, 2009 1:30 PM

All replies

  • Hi Sam,
    Does the user running the Job have access to the folder mentioned?
    If not provide it the necessary previleges.
    Hope this helps !! Please mark the post(s) as “Answered / Helpful” that helped you - Sudeep
    Monday, August 17, 2009 1:34 PM
  • Hi Srpatel,
    Please check the access to that particular folder...
    If it has the access then prepare the configuration with the password for Access DB.


    Let me know if you have any more questions on this.


    Please "Mark As Answer" , If the post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    Monday, August 17, 2009 3:35 PM
  • Hi,

        Thanks for the reply.

        In job, under steps, I changed the path for the source in data sources and error regarding the path has been removed but still I am getting the below error.

    Executed as user: DOMAIN\Administrator. ... 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  3:31:01 PM  Error: 2009-08-17 15:31:08.19     Code: 0xC0202009     Source: SSISImport Connection manager "DestinationConnectionOLEDB"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Client unable to establish connection".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Failed to read prelogin response.".  End Error  Error: 2009-08-17 15:31:08.20     Code: 0xC020801C     Source: Data Flow Task Destination 3 - NSN Master [437]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DestinationConnectionOLED...  The package execution fa...  The step failed.

       Would appreciate the reply.  

    Sam.

    Monday, August 17, 2009 8:02 PM
  • Hi,
    Which connection is being used by : "Data Flow Task Destination 3 - NSN Master"

    Can you check what kind of authentication is being used for this connection.
    Is the server to which the connec is being made listning for requests?

    Can you connect to the server by some other way??
    Hope this helps !! Please mark the post(s) as “Answered / Helpful” that helped you - Sudeep
    Monday, August 17, 2009 8:05 PM