I was getting the following error with a SSIS package I deployed on the server: Failed to decrypt protected XML node “DTS:Password”.

I spend a lot of time on this error, before I figured this one out. At first it wasn’t clear to who or what caused this error. After investigation I noticed the error occurred only when I added an Execute Package Task.

Apparently you can set an password on a SSIS package and the Execute Package Task needs this password to run the specified SSIS package.

Execute Package Task Editor

But mine SSIS packages didn’t have a password. So how could this be a problem? This is where SSIS package encryption comes looking around the corner.

SSIS Package encryption

If you open a SSIS package, you can modify the properties of it, by selecting a blank spot in the Control Flow tab, right click and then Properties.

Protectionlevel

There is a property called ProtectionLevel which determents when and how sensitive data should be encrypt. Sensitive data is date like passwords, like a SSIS Package password.

With the default value EncryptSensitiveWithUserKey all sensitive data will be encrypted with an hash key, that is created from the current user, designing the package. This is the reason why this error occurs, only happens when you deploy the SSIS package. On the server it is running under a different user, which generates a different hash.

But the strange thing was, that I didn’t put a password on the SSIS package, so why does it fail?

The problem is that even if the password of an SSIS package is blank, it will still be encrypted with this hash and it will therefore throw this error when running under a different user.

How to solve it

To solve this problem, you should change the called SSIS package in the Execute Package Task. Change the property ProtectionLevel to DontSaveSensitve. This will not store any password with SSIS package (so it doesn’t need to hash them) and running from Execute Package Task will now succeeded.

You can also use ServerStorage of you deploy in SQL Server.

Keep in mind that al sensitive data, like all password-part in database connections, are also not stored with the package and needs to be given at runtime, using Package Configurations.