Storing Password in SSIS Packages

Recently I had to make an SSIS package where data was loaded from an external database (Oracle in my case) to a SQL Server 2005 database.

All went well except one thing. How to save the Oracle and SQL 2005 connection credentials encrypted in the package? I wanted to store the credentials in a way that a user with rights to execute the package, could do this without seeing the credentials of the databases the package connects to.

I tried all kind ProtectionLevel settings in Business Intelligence Development Studio but nothing worked. I tried with a configuration file, which worked but this left the connection data visible for people with access to the configuration file.

The only ProtectionLevel I did not managed to save the package with was ServerStorage. When I tried to save the package I always got the following message:

In the end it seemed that the only way to set this property and to save the file without error is using Save Copy of Package.dtsx As….

1. Select File > Save Copy of Package.dtsx As …
   
2. Select the server and location where you want to save the package and click on the button with a dot next to the greyed out protection level.
   
3. Now you can change the package protection level to the last option “Rely on server storage and roles for access control”.
   

Now everyone that has access to execute the package will be able to execute it without getting errors that the credentials of the datbases the package connects to are invalid or “Failed to decrypt protected XML node …”  errors.