Wednesday, October 3, 2007

Moving SSIS packages from Development to Production

I built a few packages on my development machine and it worked fine without any major hiccups. Then after moving the packages to the destination server, it started throwing weird errors. I had first changed the connection strings in the package configuration file but still the following errors started showing up :

First Error:

a)
Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

b)Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

This happens because of the package protection level which is by default set to "EncryptWithSensitiveKey". Setting the package protection level to "DontSaveSensitive" helped me to remove these errors. This can be done by right clicking the package in the designer and changing the properties
Also, I setup the proxy sever agent so that the package runs under my account(the one which created the package)

Second Error:

After the first error was resolved, now was the time to run the packages. When I ran the package, it threw an error which was something like this:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "*****" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed

The error was happening in an access datasource. I went back into the package configuration file and removed the initial catalog parameter value(which was empty by the way) and voila, it worked.
This post helped me to get through :
https://forums.microsoft.com/msdn/showpost.aspx?postid=73863&siteid=1

5 comments:

kiran said...

But I created a web service task and i have credentials in the HTTP Connection Manager. If i do not set the protection level to "EncryptWithSensitiveKey", the credentials will not be saved.
What will be the solution if i am still getting the error Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state." and i still need to set the protection level to "EncryptWithSensitiveKey".

Thanks

chris said...

This document has more information:http://www.fulltimedba.com/CategoryView,category,SSIS.aspx

fix said...

Thanks, this was succefully for us.

Nathan said...

thanks a lot, this is very helpfull.

Anonymous said...

Thanks a lot. This is very helpful

Post a Comment