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

▲
33
▼

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?

sql-server-2008-r2
asked on Stack Overflow Jun 22, 2013 by Gayanee Wijayasekara • edited Feb 27, 2015 by Jeremy S.

6 Answers

▲
45
▼

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 DB or anyother stuff), the default setting is "EncryptSensitiveWithUserKey"... You need to change this.

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

answered on Stack Overflow May 8, 2014 by mmassey10 • edited May 23, 2017 by Community
▲
14
▼

Change both Project and Package Properties ProtectionLevel to "DontSaveSensitive"

answered on Stack Overflow May 14, 2015 by Rashmi
▲
11
▼

It is because creator of the SSIS packages is someone else and other person is executing the packages.

If suppose A person has created SSIS packages and B person is trying to execute than the above error comes.

You can solve the error by changing creator name from package properties from A to B.

Thanks, Kiran Sagar

answered on Stack Overflow Oct 17, 2013 by Kiran Sagar
▲
3
▼

Little late to the game but i found a way to fix this for me that i had not seen anywhere else. 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

enter image description here

I could not for the life of me find another solution but this worked!

answered on Stack Overflow Feb 13, 2018 by Leonardo Wildt
▲
0
▼

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. Once these match the issue should be resolved (for some)

answered on Stack Overflow Sep 10, 2018 by Turner
▲
0
▼

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

answered on Stack Overflow Feb 21, 2019 by Janatbek Orozaly

User contributions licensed under CC BY-SA 3.0