Jul
3
2012

SSIS package 2k5-> 2k8 R2 upgrade failed with error – Failed to decrypt protected XML node “DTS:Property”…

Scenario is when you are trying to open or upgrade SSIS package created in SQL 2005 to SQL 2008 R2 then you may encounter below error and your package upgrade will fail, please find few tips on resolving this error below.

 

Error Description: Failed to decrypt protected XML node “DTS:Property” 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. 

 

Error may occur when following criteria are met:

a) Upgrading SSIS 2005 package to SSIS 2008/R2 package

b) Executing SSIS package under different authentication other than the user id under which package was built

 

Error Reason:

This error generally occurs because of DTS package Security settings ‘protection level’, if it is set correctly then there are less chances of such error.

 

Microsoft has described this error by design and provided some workaround at following link: http://support.microsoft.com/kb/918760

Meaning of different Protection level settings: http://technet.microsoft.com/en-us/library/ms141747(SQL.90).aspx

 

Solution:

Microsoft has provided some solution as per KB article 918760. Please find below some solutions including how to change protection level and get over this error,

 

Solution 1: Open the 2005 package in BIDS 2008 while logged in as the user that originally created the packages

Solution 2: Open the 2005 package in BIDS 2005, change the ProtectionLevel to EncryptSensitiveWithPassword, and save the package. Open the 2005 package in 2008, supply the password, and save it.

 

Solution 2 in detail is taken from following forum link: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8519452c-67cb-4864-89de-c0042a82b38c/ and extended information is provided on how to schedule the updated package via SQL Job

 

1. Let’s Say your package name is Package1

2.  In Visual Studio,  Go to Control Flow Tab.

3. Right Click on an empty area inside the window not clicking  “Data Flow Component”, on pop up menu click the properties to get to the properties window of Package1 package.

4. Under the Security Area -> You will see   

        ProtectionLevel              — Change that to EncryptSensitiveWithPassword

        PackagePassword          — enter password->  temp1

5. This should do the trick however to be sure:

In connection managers, Database Connections (if more than one preform on all)

 -Double Click your connection to get the property pages. Click “ALL” under the Connection Link on Left Side. Scroll Down to Security Area.

 -Provide the followings:

 -Password  (for the sql userid being used)

 -Persist Security Info  = True

 

Note: Now based on your requirement, you can save dts package solution and open in BIDS 2008/R2, supply password temp1 and upgrade package. Later, if want to deploy on SQL server then see extended info below.

 

—-Extended Info——

6. Save the Package and connect to SQL Integration services in SQL Manager  (To Server e.g. DBServer (Integration Services) )

7.  Stored Packages -> MSDB –>  Right Click –> choose Import Package

8. In the property dialog box

      -Package Location :   File System

       -Package Path  — Choose the location of your dtsx file.  (Package1.dtsx)

 

9. Leave everything default.

10. Click OK.

11. Dialog box will appear asking for the Package Password

12. Provide the password-> temp1

13. You have successfully imported the package called Package1.

14. In order to create a job.   

15. In the job Step->

      -Type:  SQL Server Integration Services Package

       -In the General Tab:

          –Package Source :  SSIS Package Store

          –Server : DBServer  (Where we stored our package above)

16. Click the button for the package:  Choose your package  (Package1)

17. Click OK :

18. It will ask the package password again :  temp1

19. Package has successfully been loaded to Job Step.  Now you can schedule and do a test run on the job.

20. Document the package password somewhere for other developers else it may become a roadblock

Note: To avoid password issue, while importing to SQL Server, you can also choose the last option in the Import Package dialog box i.e. “Protection Level” and change it to “Rely on server storage and roles for protection level”. This will avoid password issues later.

 

Useful References:

http://www.networksteve.com/enterprise/topic.php/SSIS_Package_Upgrade/?TopicId=38949&Posts=5

http://dbaspot.com/sqlserver-dts/364022-password-failed-decrypt-error-when-set-not-encrypted.html

 

Hope this would have solved your issue as it did in mine case.

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 184 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

July 2019
M T W T F S S
« Nov    
1234567
891011121314
15161718192021
22232425262728
293031  

View Post by Categories

%d bloggers like this: