May 14, 2014 at 2:40 am
I have a SSIS package that is executing properly as through BIDS but it is failing when I am trying to configure it as an agent job with an error message as below:
Executed as user: SICAT01\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:28:59 PM Error: 2014-05-14 12:29:00.98 Code: 0xC0202009 Source: PackageName Connection manager "production.sa" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2014-05-14 12:29:00.98 Code: 0xC020801C Source: FormMap Staging DFT DestinatioN Table [277] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "production.sa" 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: 2014-05-14 12:29:00.98 Code: 0xC0047017 Source: FormMap Staging DFT SSIS.Pipeline Description: component "DestinatioN Table" (277) failed validation and returned error code 0xC020801C. End Error Error: 2014-05-14 12:29:00.98 Code: 0xC004700C Source: FormMap Staging DFT SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-05-14 12:29:00.98 Code: 0xC0024107 Source: FormMap Staging DFT Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:28:59 PM Finished: 12:29:00 PM Elapsed: 1.109 seconds. The package execution failed. The step failed
May 14, 2014 at 2:48 am
Hi Looks like your credentials are incorrect on the job:
"Login failed for user 'sa'."
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 14, 2014 at 2:55 am
Andy Hyslop (5/14/2014)
Hi Looks like your credentials are incorrect on the job:"Login failed for user 'sa'."
Andy
+1
When a package runs in BIDS but not in Agent it is 90% of the time a permission issue.
Since it is the sa user, I think it has the required permissions since it belongs to the sysadmin role.
But because it is a SQL Server user, you need to supply a password and I think that is where something went wrong.
What is the protection level of the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 3:38 am
The protection level is "Encryt All with password" and the SQL agent services is having - log on as "LocalSystem". This is the first job being created on the server, I have tried all the protection levels, none of them worked. Besides, the user sa is having sysadmin role and I have also tried both 64bit as well as 32bit runtime environments. Any clue on this?
May 14, 2014 at 4:08 am
Is the password for the connection manager stored in the package, or provided through package configurations?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 5:03 am
Password to the connection manager is stored there itself. Package configuration is disabled.
May 14, 2014 at 5:04 am
chandrakant_gaurav (5/14/2014)
Password to the connection manager is stored there itself. Package configuration is disabled.
Just for kicks, does it work when you use a proxy in the SQL Server Agent job?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 5:19 am
I tried using proxy account but it didnt work either.
May 14, 2014 at 5:22 am
chandrakant_gaurav (5/14/2014)
I tried using proxy account but it didnt work either.
Well, there must be something wrong.
Are you connecting to the correct server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 6:29 am
I was able to resolve this error. While deploying the package, in "data sources" in the connection string I added - Password = <my DB password>; and it worked. It was very small but very painful issue.
May 15, 2014 at 1:52 am
Check whether job owner has access permission to database
Check proxy account
Check 32 bit run option
Check connection configuration is supplied via package configuration or manually passed via expression or package protection option.
May 15, 2014 at 1:57 am
Yadava Shettigar (5/15/2014)
Check whether job owner has access permission to databaseCheck proxy account
Check 32 bit run option
Check connection configuration is supplied via package configuration or manually passed via expression or package protection option.
1. The issue was already resolved. (and neither of your points had anything to do with it)
2. Why on earth would running a package in 32-bit or 64-bit solve a permission issue?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply