Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Login Error Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 6:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 590, Visits: 1,579
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
Post #1425021
Posted Thursday, February 28, 2013 11:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:17 PM
Points: 124, Visits: 485
Does the SQL Server Job Agent have permissions?
Post #1425215
Posted Thursday, February 28, 2013 1:57 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 590, Visits: 1,579
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.
Post #1425276
Posted Thursday, June 6, 2013 11:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 308, Visits: 810
I would think the agent credentials are used and not necessarily overridden by something else on the SSIS package.
Post #1460822
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse