We have parent and child SSIS packages. Both the packages are using environment variable configuration to get the confuguration database and sql configuration to get the further configuration property. Both the packages have sql authentication and package protection level is “EncryptSensitiveWithUserKey”. Both the packages run as expected in development environment but when moved to QA, even though the child package executed successfully the “Execute Package Task” reports failure making the parent package fail.
When the package is moved to a separate environment, it cannot decrypt the password saved within the SSIS package and reports the following error when loading the package
Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state
Later on, it gets the connection string from configuration and successfully executes.
Even though this is an error, when executing the package individually from command prompt or from Visual Studio Data Tools, it is “ignored” and the package reports success. When the same package runs through the “Execute Package Task”, it is reported as an error and as the maximum error count is set to 1, this task reports failure even when the underlying package ran successfully
In my situation, I changed the package protection level to “DontSaveSensitive” and everything work as expected. The same settings also work in the development environment because as soon as I open the package, it gets the credential from configuration so I don’t have to manually key in the sql credential every time I open it.