SSIS issue Failed to decrypt protected XML node

Question

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.

Eisinger

hey why close please help me With same error message I was getting only warning but today job gets failed why ? please help i'm newbie – Eisinger

What has changed to the package and or method of invocation to result in the failure? – Firn

Did you access it from a different computer than you used to work? – Inexperience

I am having the same problem , solution provided here does not work – Nativity

Answer 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.

Trafficator

Answer 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:

Stringpiece

Answer 3

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

Glucose

Answer 4

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

   Run64BitRuntime = False

Matthews

or, goto Job Step properties->Execution options-> check Use 32 bit runtime. anyway, It didn't helped in my case. – Delete

Answer 5

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.

Abiogenesis

Answer 6

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

Iminourea

Answer 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.

Eugenle

Answer 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!!

Jollenta

Answer 9

If your project has other packages, then it may not be wise to change the "ProtectionLevel". Moreover, if it has other packages and you want to change the "ProtectionLevel" then you have to change it for all other packages.

A workaround solution can be to use the "Variables" for the "ServerPassword".

1. Right click on the control flow (any blank space) -> Click "Variables"
2. Click "Add Variables" from the top left corner of "Variables" window.
3. Define "Name", and "Scope". Choose "String" for "Data type". In the "Value" give your password.
4. Now go to the properties of "FTP Connection Manager".
5. Click "Expressions". Then click the three dots "..." from the right of that row. It will bring the "Property expressions" window.
6. Choose "ServerPassword" as a "Property" for a row.
7. For that row, go to the "Expression" column and click the three dots "...".
8. In the "Expression Builder" window go to the "Expression:" subwindow. Drag and drop the variable you created. Click "Evaluate Expression" to check the value is working. Click "OK".

As a result, this FTP connection password will not be affected by the "ProtectionLevel" of the project anymore and you don't need to change the other SSIS packages of the project.

Mango