I am developing an SSIS package in a local computer. I have connection managers defined that link to a SQL Server database. I connect to the database using SQL Server Authentication, a username and a password. When I copy the package files and try to execute them in another computer, the password is lost and I have to edit the package and rewrite the password. How can I move a package between computers and not lose the password
Regards,
Pedro Martins
Moving from one computer to another
regina
Passwords will not be stored in a package except in an encrypted format. You can affect this using the Protectionlevel property of the paclage.
I recommend that you look to use configurations. These are the built-in method for moving your packages between environments. At the moment your package is location-dependant and this is not a good situation and is definately not best practice.
-Jamie
Doug Mitcham
The default package protection mode is Protect Sensitive Data with User Key. It uses DPAPI to automatically protect passwords in the package using your NT password.
This is very convinient: you don't have to type any password when you open a package. But the limitation of DPAPI is that it only works across machines if you are using roaming profile. Also, obviously only the same user who created the package can decrypt the sensitive data.
If you want to share packages across machines or between multiple users, you need to switch packages to Password protection mode (it is a property of the package) and enter a password. You'll need to enter this password each time you open a package though.
Another option is to use configs as Jamie recommends.
Roman T
I am still confused about configurations. How can I define a configuration so that I can maintain the connection managers' passwords when I move packages between environments
Pedro Martins
JimKillian
Store the ConnectionString property of the connection manager in an XML configuration file.
You will notice that it does not put the password in there. SSIS will not do this for you as it is considered a security risk - Microsoft want YOU (not them) to be liable for storing the password in there. Hence, you have to edit the file yourself and put the password in there.
-Jamie