SQL Server Jobs with SSIS packages - Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

Question

i have a SQL server job that runs a SSIS package. This job has 9 steps and in each step it extracts data from a different database. the connections strings are defined as parameters in each step.

im getting the following error when i run the job.

Executed as user: USER\MYSERVER$. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started:  5:50:55 PM  Error: 2013-06-21 17:50:55.44
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2013-06-21 17:50:55.45
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2013-06-21 17:50:55.45
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2013-06-21 17:51:06.30
Code: 0xC020901C
Source: Data Flow Task Daily Attendance View 1 [34]
Description: There was an error with output column "ShiftCode" (54) on output "OLE DB Source Output" (45). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".  End Error  Error: 2013-06-21 17:51:06.30
Code: 0xC020902A
Source: Data Flow Task Daily Attendance View 1 [34]
Description: The "output column "ShiftCode" (54)" failed because truncation occurred, and the truncation row disposition on "output column "ShiftCode" (54)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  End Error  Error: 2013-06-21 17:51:06.30
Code: 0xC0047038
Source: Data Flow Task SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Daily Attendance View 1" (34) returned error code 0xC020902A.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:50:55 PM  Finished: 5:51:06 PM  Elapsed:  10.983 seconds.  The package execution failed.  The step failed.

can someone please tell me why this happens?

Manchineel

Do the packages run is Visual Studio SSDT? – Borglum

Answer 1

In addition to what Kiran's answer suggests, make sure this is set correctly:

There is an option to in SSIS to save passwords (to access a DB or any other stuff), the default setting is "EncryptSensitiveWithUserKey"... You need to change this.

Package Properties Window > ProtectionLevel -- Change that to EncryptSensitiveWithPassword PackagePassword -- enter password-> somepassword

Garceau

Is it only Enterprise future ? I can not find this on Community Edition – Humankind

to get to the Package Properties window: learn.microsoft.com/en-us/sql/integration-services/… – Echinoid

in my case, i also had to set the Project Protection Level (right click on Solution, and Properties) – Echinoid

Answer 2

Change both Project and Package Properties ProtectionLevel to "DontSaveSensitive"

Clackmannan

...which is found (in visual studio) by clicking anywhere on the control flow area. – Duplicature

right click on your project in Solution Explorer -> Properties -> Security section, you will see "ProtectionLevel" right there. – Host

Answer 3

It is because the creator of the SSIS package is someone other than the person who is executing the packages.

If person A created the SSIS package and person B is trying to execute the package, than the above error will be given.

You can resolve the error by changing the creator name in the package properties from person A to person B.

Stipitate

but if you change the creator name, you cant build anylonger. only owner can build and publish it. – Ethylethylate

Answer 4

Select your connection from Connection Managers.

On the right you should see properties. Check to see if there are any expressions there if not add one.

In your package explorer add a variable called connection to SQL or whatever. Set the variable as a string and set the value as your connection string and include the User Id and password.

Back to the connection manager properties and expression. From the drop down select ConnectionString and set the second box as the name of your variable. It should look like this:

Undershrub

Answer 5

For me the issue had to do with the parameters assigned to the package.

In SSMS, Navigate to: "Integration Services Catalog -> SSISDB -> Project Folder Name -> Projects -> Project Name"

Make sure you right click on your "Project Name" and then validate that 32-bit runtime is set correctly and that the parameters that are used by default are instantiated properly.

Check parameter NAMES and initial values. For my package, I was using values that were not correct and so I had to repopulate the parameter defaults prior to executing my package.

Check the values you are using against the defaults you have set for your parameters you have set up in your SSIS package.

Foretopgallant

Answer 6

In my case it was because I didn't connect to databases yet when first opened solution. click connection manager tab, establish connection to every datasource in that tab, run project

Millesimal

Answer 7

I generated a SSIS export package from the SQL Server Management Studio export wizard saving the package with sensitive information encrypted by a password.
I created a SQL Agent job to run the export. I kept getting the decryption error. The issue was one (or more) of the special characters in the password. I got rid of slashes (forward and backward) along with quotes and apostrophes and it worked.

Sapir