SSIS Login Error

  • Hi all

    I have created a SSIS package that contains an OLEDB connection to a database using a SQL login on the server from which the package is running. This all works in Visual Studio 2010.

    I then scheduled this to run in a SQL job however I am getting an error of:

    An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'XXX'.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'XXX'.". End Error Error: 2013-02-28 13:14:09.82 Code: 0xC020801C Source: Import XXX OLE DB Destination [2] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "XXX" 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-02-28 13:14:09.82 Code: 0xC0047017 Source: Import XXX SSIS.Pipeline Description: OLE DB Destination failed validation and returned error code 0xC020801C. End Error Error: 2013-02-28 13:14:09.82 Code: 0xC004700C Source: Import XXX SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-02-28 13:14:09.82 Code: 0xC0024107 Source: Import Zetafax1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 13:13:54 Finished: 13:14:09 Elapsed: 15.693 seconds. The package execution failed. The step failed.

    I had thought his might have been the issue with the job step needing to run under a proxy, but I created a credential under me (I am an SA) and assigned the proxy to the job but I am still getting the same error. The account in question has permissions to the database, so am unsure what is happening?

    Thanks

  • Does the SQL Server Job Agent have permissions?

  • Well I changed the connection in the package to a windows account and now it works by the proxy, however I don't know why it wasn't working using a SQL account.

  • I would think the agent credentials are used and not necessarily overridden by something else on the SSIS package.

    ----------------------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply