I am running the Sept CTP. I have created a SSIS package that pulls data from a text file and inserts into a database. When I run the package on the DB server it runs fine. I have moved the package the the web server (we are attempting to kick off the package from a web site) and we get connections errors. We are running the package using dtexec from the command line.
The 1st time we got
The AcquireConnection method call to the connection manager "Program DW" failed with error code 0x80004005.
One post I read said that changing the DelayValidation to true may help.
I set the DelayValidation to true on all the packages and I now get this error:
Failed to acquire connection "Program DW". Connection may not be configured correctly or you may not have the right permissions on this connection.
What do I need to do to make sure that the permission is configured properly?
Does the package use stored credentials for the database? If so, your problem may not be because you moved the package, but because it is being run in a different security context. By default, SSIS secures stored sensitive data with a user key. If you access the package as another user, that sensitive data won't be available. That may be what is happening. You may need to change the ProtectionLevel to EncryptSensitiveWithPassword and then run dtexec with that password from your web page.
See "Setting the Protection Level of Packages" in BOL.
- Proposed as answer by piris Wednesday, October 03, 2012 3:49 PM
I have turn the security setting to DontSaveSensitive. The master package has a variable called dbConnection and we store the entire connection string. It is passes to all the packages and we have an expression set the connection string.
Did that work, you didn't actually say whether it did or not? I would be surprised if it did not.
I do something similar where I store the connection string in a configuration. Security needs to be enforced at some point and in this instance you need to make sure that no-one can get onto the box that shouldn't be allowed to - otherwise they could just open the config file in notepad and get the password.
We were not able to resolve this issue. The SQL 2005 SP1 CTP (March 2006) release may help.
I was able to resolve this issue by manually editing my dtsx file in a text editor. The issue was caused when I was setting up a Package Configuration in which I inadvertantly selected the option to save the inital catalog. I created a new connection from scratch which worked. Then in the text editor I looked for differences. In the DTSX file the catalog was stored as "Initial Catalog=;" (semi-colon). I removed the entire tag for the initial catalog and then it worked. If you are going to try this make a copy first in case of bad editing. Also, I'm sure Microsoft won't recommend this option.
Best of luck.
- Proposed as answer by laurensp Wednesday, October 26, 2011 8:56 AM