Load SSIS package using Application.LoadFromSqlServer() with an encrypted connection string

Question

I'm using

var app = new Application();
var pkg = app.LoadFromSqlServer(ConfigurationManager.AppSettings["SSISPackagePath"],
                                ConfigurationManager.AppSettings["SqlHost"],
                                ConfigurationManager.AppSettings["SqlUser"],
                                ConfigurationManager.AppSettings["SqlPass"], null);

to load an SSIS package from sql server for use in an application.

Using the DTS command it requires a /DECRYPT option with password to decrypt the sensitive information stored in the package.

Without the password I get the following error message:

ADO NET Destination has failed to acquire the connection {DB515DE9-B006-4BFC-AC53-ABED0B77183C}. The connection may have been corrupted.

component "ADO NET Destination" (841) failed validation and returned error code 0xC0208452.

How do I pass the decrypt password to the package using C# so it can execute without error?

Did you made sure about the basic security stuff like the SSIS package has permissions for this user and the databases the SSIS package is accessing can also be accessed by the user etc. etc. — Azhar Khorasany
@AzharKhorasany Yes, i went though all that just to get to the point of needing the decrypt password. — John Boker
I was under the impression that when you saved to SQL Server, you do not require encryption (or the server does it for you) technet.microsoft.com/en-us/library/ms141747(v=sql.90).aspx Can you verify what protection level you used to save this package? — Nick.McDermaid

Tomas Tomas · Accepted Answer · 2013-04-27T08:57:57

Have you tried the Application.PackagePassword setter property? Link to documentatation: http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.packagepassword.aspx

This method is also mention in this SO question: How can I execute a package with a package password in SSIS through code?
NB according to that link you have to specify the password on the application BEFORE loading the package.

Load SSIS package using Application.LoadFromSqlServer() with an encrypted connection string

1 Answers