SSIS agent job failed. Description: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state
Sometimes a perfectly running package fails to run when it is configured to run on agent job. This error ” Failed to decrypt protected XML node” usually comes as a warning and it sometime become a reason to fail. While this error occurs whenever there is a sensitive data in the package. For example in my case I have a script task that generates an XML file for a folder structure and the server feels it needs to be encrypted.
In order to resolve this follow this steps.
- Click on any part of your SSIS package control flow then from the security section select EncryptSensitiveWithPassword for ProtectionLevel and enter a password for PackagePassword. Let’s say “Pa$$w0rd “
2. On management studio find edit the job. Go to the step that is causing a problem and click on Edit.
3.Click on Command line and at the end of the command line existing text add /DECRYPT and the password used in the package originally, while in our case it is /DECRYPT Pa$$w0rd. Click on Ok.
That should solve the problem.
Good luck.