Sunday, June 29, 2008

SSIS, VSTS and Sensitive Information

Hello everybody.
I have been testing for a long time what is the best approach to share SSIS packages between developers in a wide developing team. The best solution for controlling versions of SSIS packages is, without a doubt, Visual Studio Team System. With this tool the developers have full control of versions, branches, documentation, etc.
But there's an issue when we share a SSIS package in a environment like this. SSIS encrypts all sensitive information about the package (passwords basically). By default, SSIS encrypts this sensitive information with a key that depends on the machine that contains the package. When the package is saved and another developer tries to edit it, SSIS tries to decrypt the package with the wrong key and fails.
There are two possible solutions to this issue:
1) Set the ProtectionLevel of the package to EncryptSensitiveWithPassword and manually enter a password and share it with all developers. This approach allows the programmers to validate the package in design time and save the sensitive information inside the package. This approach has the inconvenient that every time that a developer opens a package, it is mandatory to enter the correct password. If we have 10 packages opened in a SSIS project, the developer will have to enter the password 10 times before starting to program.

2) Set the ProtectionLevel of the package to DontSaveSensitive, use Package Configurations and set the DelayValidation property to True. With this approach the developers can save the sensitive information in XML configuration files, and there's no need to supply any password. Using package configurations is also considered a best practice by Microsoft experts.

In my opinion, the best solution in most cases will be the second one. In addition to the control over the sensitive information, we will have an easier way to move the package throughout environments (developing, testing, UAT, pre-production and production).