I am trying to deploy an SSIS package that I created. Within the SSIS
package, there is an ADO.NET connection manager that contains my connection
information. This runs fine from Visual Studio on my machine. However,
after I deploy the package to the SQL Server 2005 machine using the manifest
file and the package deployment utility, the password is not saved in the
connection string even though I have "Allow Saving Password" checked.

I cannot figure out how to get the password to be saved to the server so the
connection does not have to be modified each time the package is run. I need
to hook this package up to a job and since the password isn't being saved,
it's not possible at the moment.

The requirements are:
The package needs to be in the package store on the server.
The package has to use a SQL Server Authentication User.
The package has to be hooked to a job to run on schedule.

I looked into using a configuration file, but I cannot figure out how to get
the user name and password in the configuration file and have them encrypted.
If anyone knows how to use this method, please let me know.

Hello Aaron,

You should be looking to Configurations to do the Password type of stuff.
You do not encryot the file you simply make sure that is only available
to those that need to know it. This is if you are using an XML file but
you could also lock down the config if it was in a DB table.

What is the setting of your package's ProtectionLevel property?



Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Allan,

Okay, I am now using the configurations to handle the password and it works
fine. The setting for my package's protection level property is
"EncryptSensitiveWithUserKey". I figure that since we can't encrypt the
file, we will handle the security with Active Directory permission sets.
Thanks for your help.


"Allan Mitchell" wrote: