Execute Package Task Fails Even Though the Package Executed Successfully

We have parent and child SSIS packages. Both the packages were using environment variable configuration and sql configuration to get the further configuration property. Sql authentication and package protection level is set to “EncryptSensitiveWithUserKey” for parent and child packages. Both the packages run as expected in development environment. When we moved packages to QA, even though the child package executed successfully the “Execute Package Task” reported failure. It caused parent package to fail.

Root Cause

SSIS package encrypts password with the machine key available on the computer on which it is developed. When we move the package to a different machine, machine key differs and SSIS cannot decrypt the saved password. It 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.

If I execute package from command prompt or from Visual Studio, it “ignores” the “error” and the package reports success. When I ran the same package through the “Execute Package Task”, it reports as an error. SSIS package maximum error count was set to 1 so this task reported failure on the first occurrence of error even though the underlying package ran successfully

Solution

In my situation, all the packages were getting configuration from environment. Packages were not using saved credentials. I changed the package protection level to “DontSaveSensitive” and everything work as expected.