CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssis
Neo
Neo

Reputation: 16239

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.

Upvotes: 11

Views: 62734

Answers (10)

SeasonedSQLDev
SeasonedSQLDev

Reputation: 1

I had this issue when I came across a package not working properly all of a sudden after a change to the source data connection via ODBC to a new migrated server. Then I kept getting a "connection to the server was forcibly closed" issue. I discovered that the same package continued to run successfully on the DEV server even though the build and ODBC connectors were identical. So, I decided to fetch the package from the DEV system and pull it into the LIVE system.

The easiest way to overcome this issue is to do the following:

  • Leave the Project level Properties setting as "EncryptSensitiveDataWithUserKey" setting
  • Temporarily change each package in the solution from "EncryptSensitiveDataWithUserKey" to "DoNotSaveSensitive".
  • Rebuild the solution. This produces project inconsistency errors stating that the protection level of the project is inconsistent with the following items:" (A list of SSIS packages is presented).
  • Change back each package setting from "DoNotSaveSensitive" back to "EncryptSensitiveDataWithUserKey" and rebuild
  • Rebuild is successful.

Upvotes: 0

TANMOY CHOWDHURY
TANMOY CHOWDHURY

Reputation: 39

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.

Upvotes: 2

Miki G
Miki G

Reputation: 21

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

Upvotes: 1

HARSHIT RATHORE
HARSHIT RATHORE

Reputation: 163

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.

Upvotes: 1

Veer
Veer

Reputation: 31

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

Upvotes: 3

Anup Kulkarni
Anup Kulkarni

Reputation: 126

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.

Upvotes: 11

Branislav
Branislav

Reputation: 11

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

Upvotes: 1

Bassam Gamal
Bassam Gamal

Reputation: 701

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:

enter image description here

Upvotes: 6

JohnN
JohnN

Reputation: 23

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.

Upvotes: 1

user2820151
user2820151

Reputation: 11

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

   Run64BitRuntime = False

Upvotes: 1

Related Questions