SSIS package deployment and passwords
Something to remember when deploying your SSIS package:
The server will most likely run your package under a different user than the user you used to create the package. This causes a problem with the 'ProtectionLevel'.
The default ProtectionLevel is EncryptSensitiveWithUserKey. During deployment, the user key will not be the same as yours. You need the same keys to encrypt and decrypt the connection strings and stuff like that.
This will give you the error: Failed to decrypt protected XML node "DTS:Password"
This error will only occure when you try to deploy, not while testing locally.
To fix this, change the ProtectionLevel to 'EncryptSensitiveWithPassword'.
ProtectionLevel is found in the properties of the 'Package Explorer' tab.
Then, when you create your job in the SQL Server Agent, you must enter your password.
This is done in the 'Steps' page:
- Under Type select SQL Server Integration Service Package
- Under Run as select SQL Server Agent Service Account
- Under the General tab and Package, select the path to your package (in the bid folder of your project or where ever you put it)
- Click Configurations or any other tab and you will then be prompted to enter your password in a popup.