SQL Server Jobs with SSIS packages – Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B

If you ever encounter this error on SQL Server while you’re trying to create a job to execute a SSIS package, is because the SQL Server can’t decrypt the password stored in your SSIS package.

What happened with me is the user account that signed the sensitive data of the SSIS (default option) is different from the one that runs it.

I created a SSIS with an account X and then tried to create a schedule job to run it. However the SQL Server provided for me only runs the schedule jobs with the SQL Server Agent account.

Workaround:

There is a ProtectionLevel option in SSIS to save the sensitive data such as DB connection passwords and the default setting is “EncryptSensitiveWithUserKey”.

Change that to “EncryptSensitiveWithPassword” and enter the password such as “somepassword”

On the SQL Server Agent Job Step, go to the Command Line tab and add the option /DECRYPT somepassword

For example:
/FILE "\"C:\SSIS\Package.dtsx\"" /DECRYPT somepassword /CHECKPOINTING OFF /REPORTING E