One morning, you discovered your SSIS package failed to run with the following error message:
Started: 5:00:03 AM Error: 2021-10-08 05:00:04.62 Code: 0xC0016016 Source: SSIS_Package_ITNota Description: Failed to decrypt protected XML node "DTS:Password" 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. End Error
This is typically caused by a simple setting that is too often overlooked during deployment.
In general, you do not want to save any connection ID and password in your package so you can inject the value from variables that you store either in a database or if you use a Package Deployment Model, then in an XML saved in the *.dtsConfig file.
So what you want to do before you build the package for PROD deployment, you want to set the package property Protection Level to DontSaveSensitive and you set all the values for your variables in your configuration file.
Steps to Set SSIS Package Property Level
With your SSIS package opened, under the Control Flow tab, on a blank area, right-click your mouse and select Properties or press ALT+ENTER.
Under Properties, go to Security, and look for ProtectionLevel. Check the value. By default, it’s set to EncryptSensitiveWithUserKey.
The ProtectionLevel should be set to DontSaveSensitive.
Save the package and if the configuration is already setup, it’s ready to be deployed.
