I am using SQL authentication for a SSIS connection manager for one domain to another. In Visual Studio the connection is working when tested but password does not save. Running the package in VS is working but when creating a SQL job with the package, it fails to run with error
"Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.
Please help. Thank you.
The wrong way to do this is to put your connection string with the user name and password in the Expressions property's Connection String expression. Then change the ProtectionLevel to Dont [sic] Save Sensitive.
While this works, it leaves your password as plain text in the dtsx file.
I believe one correct way to do this is to set the password for the connection string in the SQL Server Agent's New Job step window on the Data Sources tab.
Another way is to change the Protection level to Save with Password and then enter the password in the Job step's Configuration tab. Unfortunately, I don't use SSIS much anymore and haven't tested either of these methods.
User contributions licensed under CC BY-SA 3.0