Sunday, May 6, 2012

SSIS Package migration issue with the password field encryption

The below link is a lifeline to anyone stuck with SSIS packages migrations/installations.

http://msdn.microsoft.com/en-us/library/dd440760.aspx

If you set your SSIS packages with "EncryptSensitiveWithUserKey" then you would face issues when you migrate this package from one environment to the other.

The packages would defnitely run good with "dtexec" or "dtsrun" commands from the command line but the issue is only when you want to schedule your package using sql server agent.
Because sql server agent account doesn't have sufficient permissions to decrypt the password field in the package, it fails to make the connection to the specified data source and hence the package would eventually fail.

Carla's link mentioned above in MSDN clearly explains this and gives three solutions to fix the problem.

the solutions are:
1) move the permissions and controls of the package to DB roles
2) change the protection level with no encryption
3) keep the protection level with a password so you can decrypt the password in your sql server agent job

No comments:

Post a Comment