March 29, 2011 at 3:43 pm
I've created an SSIS package to pull data from AS/400 tables and populate SQL Server tables. The source connection is using HIT OLEDB to connect to the AS/400. I've used a utility login and password used for reporting and tested the connection successfully. I've manually entered the password string into the config file...yet when I run the package, I receive a DTS connection string error. The error is as follows:
Error1Validation error. Data Flow Task: OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source Connection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Any suggestions would be appreciated!
March 30, 2011 at 2:13 am
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
March 26, 2012 at 3:49 pm
Did you ever find a solution to this problem?
I have a job that executes 4 pages.
The last one fails with an invalid password.
I execute the Package from BIDS on the Server and I do not get an error.
I check allow saving password, no issues.
I select do not save sensitive data and redeploy the package into a job with one step and the job fails.:w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2012 at 3:55 pm
Welsh Corgi (3/26/2012)
Did you ever find a solution to this problem?I have a job that executes 4 pages.
The last one fails with an invalid password.
I execute the Package from BIDS on the Server and I do not get an error.
I check allow saving password, no issues.
I select do not save sensitive data and redeploy the package into a job with one step and the job fails.:w00t:
By selecting do not save sensitive data you are telling SSIS to not store the password or any other sensitive data. It is dropped from the package.
March 26, 2012 at 4:00 pm
Check out this link: http://msdn.microsoft.com/en-us/library/ms141747(v=SQL.90).aspx.
March 26, 2012 at 4:03 pm
Lynn Pettis (3/26/2012)
Welsh Corgi (3/26/2012)
Did you ever find a solution to this problem?I have a job that executes 4 pages.
The last one fails with an invalid password.
I execute the Package from BIDS on the Server and I do not get an error.
I check allow saving password, no issues.
I select do not save sensitive data and redeploy the package into a job with one step and the job fails.:w00t:
By selecting do not save sensitive data you are telling SSIS to not store the password or any other sensitive data. It is dropped from the package.
ok, thanks. I wonder why it works for the other 3 packages in the job?
I'm going to start to test a job that has 3 packages with data from a different Source Database.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2012 at 4:13 pm
Lynn Pettis (3/26/2012)
Welsh Corgi (3/26/2012)
Did you ever find a solution to this problem?I have a job that executes 4 pages.
The last one fails with an invalid password.
I execute the Package from BIDS on the Server and I do not get an error.
I check allow saving password, no issues.
I select do not save sensitive data and redeploy the package into a job with one step and the job fails.:w00t:
By selecting do not save sensitive data you are telling SSIS to not store the password or any other sensitive data. It is dropped from the package.
The others are encypt with sensitive key.
The one that was failing had the same setting.
On the ones that I did not change I have to option to change the protection level but on the one that fails I do not have the property level property?
:crazy:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2012 at 4:20 pm
All of the steps in the second job failed with the same error.
All three packages protection level property is set to EncryptSensitiveWithUserKey.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2012 at 4:25 pm
Welsh Corgi (3/26/2012)
All of the steps in the second job failed with the same error.All three packages protection level property is set to EncryptSensitiveWithUserKey.
Are you providing the UserKey when executing the packages in the jobs?
March 26, 2012 at 6:02 pm
Lynn Pettis (3/26/2012)
Welsh Corgi (3/26/2012)
All of the steps in the second job failed with the same error.All three packages protection level property is set to EncryptSensitiveWithUserKey.
Are you providing the UserKey when executing the packages in the jobs?
It does not appear to be a package level property issue.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2012 at 7:14 pm
Welsh Corgi (3/26/2012)
Lynn Pettis (3/26/2012)
Welsh Corgi (3/26/2012)
All of the steps in the second job failed with the same error.All three packages protection level property is set to EncryptSensitiveWithUserKey.
Are you providing the UserKey when executing the packages in the jobs?
It does not appear to be a package level property issue.
IIRC, you have to provide it as part of the cmdline or through a config file.
March 27, 2012 at 5:17 am
I woke up in the middle of the night and I realized what might be the problem.
Prior to having this problem I could not start the SQL Server Integrated Services Service.
It was set to log on as NT Authority\Network Service.
It kept failing so I changed it to Local Service. :blush:
I stop the service and the job is running fine.
I need to have accounts created for SQL Server other than the built in accounts.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 9:46 am
Thanks Welsh Corgi!
Your post fixed my issue. (start up as local service)
January 21, 2014 at 9:55 am
bsantosh6 (1/21/2014)
Thanks Welsh Corgi!Your post fixed my issue. (start up as local service)
Cool glad that worked for you.:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply