SSIS issue Failed to decrypt protected XML node

问题:

I created an SSIS package which is having ftp pull files from ftp server and save to my local drive but I'm getting this issue.

With same error message I was getting only warning but today the job fails.

Message:

Executed as user: cam\Package.Runner. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:00:00 AM Error: 2012-02-15 10:00:00.61 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: 2012-02-15 10:00:00.62 Code: 0xC0016016 Source: 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. End Error Error: 2012-02-15 10:00:33.53 Code: 0xC0029183 Source: Principal Balance File FTP Get FTP Task Description: File represented by "/Concerto/Virtus_Reports/Concerto Principal Balance Report*.pdf" does not exist. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:00:00 AM Finished: 10:00:33 AM Elapsed: 33.088 seconds. The package execution failed. The step failed.

回答1:

You can fix this issue by setting the Protection Level property

Protection Level : DontSaveSensitive

With this property, the package will not be password protected, and another server can access and execute any job with other credentials.

回答2:

While importing the package to SQL Server choose Protection Level: Either

1- Don't save sensitive data.

Or

2- Rely on Server Storage and roles for access control.

Screenshot from SSIS Project Package Properties:

回答3:

Before Building and deploying the package, please be sure you've changed the property of the solution like this :

   Run64BitRuntime = False

回答4:

I got the same error message for FTP Connections. I think it was caused by me opening the Package while running BIDS under different credentials to those I created it with.

As a clunky workaround I deleted and re-created the FTP Connection. It worked fine afterwards.

回答5:

Main part of your SSIS job error is

" 0xC0029183 Source: Principal Balance File FTP Get FTP Task Description: File represented by "/Concerto/Virtus_Reports/Concerto Principal Balance Report*.pdf" does not exist. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:00:00 AM Finished: 10:00:33 AM Elapsed: 33.088 seconds. The package execution failed. "

It seems that you don't have pdf file on path you have configured in your SSIS package. Please,check up path and pdf files for import. Best regards, Branislav

回答6:

Please try save your package with the option "EncryptSensitiveWithPassword".

Step-1: Right click on your FTP connection manager, go to its Properties (the very bottom, not the Edit button), and type in the password.

Step-2: Save your package with EncryptSensitiveWithPassword.

Step-3: Now edit the command ling in SQL job agent as below /FILE "C:\Fullpath of SSIS pkg.dtsx" /DECRYPT password

回答7:

In case of FTP Connection, you can just create one Script Task before FTP task and set a password for that for example

ConnectionManager FTPConn;

FTPConn = Dts.Connections["FTP Connection Manager"];

FTPConn.Properties["ServerPassword"].SetValue(FTPConn,Dts.Variables["FtpPwd"].value);

and in case of OLE DB you can just add password in the connection string of OLEDB Connection.

回答8:

This are the steps that worked for me.

1. copied the package to another folder (just to be safe)
2. Set the Protection Level to EncryptAllWithPassword and gave it a Password like 'Test'
3. changed the creator Name to another User that I created
4. Recreated the job
5. created a Proxy to run the job

Worked!!