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

User · Answer

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

User · Answer

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

User · Answer

Change both Project and Package Properties  ProtectionLevel to  DontSaveSensitive

User · Answer

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

User · Answer

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    I could not for the life of me find another solution but this worked

User · Answer

For me the issue had to do with the parameters assigned to the package    In SSMS  Navigate to    Integration Services Catalog - gt  SSISDB - gt  Project Folder Name - gt  Projects - gt  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

[sql-server-2008-r2] SQL Server Jobs with SSIS packages - Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

Examples related to sql-server-2008-r2