Using version control with SSIS packages (saving 'sensitive' data)

Question

Hi.

We are a team working on a bunch of SSIS packages, which we share using version control (SVN). We have three ways of saving sensitive data in these packages :

• not storing them at all
• storing them with a user key
• storing them with a password

However, each of these options is inconvenient while testing packages saved and committed by an other developer. For each such package, one has to update the credentials, no matter how the sensitive data was persisted.

Is there a better way to collaborate on SSIS packages?

Answer 1

Since my workplace uses file deployment, I use "Don't save sensitive" In order to make development easier, we also store config files with the packages in our version control system, and the connection strings for the development environment are stored in the config files. The config files are also stored in a commonly named folder, so if I retrieve the config files into my common config file area, then I can open any of our project packages and they will work for me for development. When the packages are deployed, they are secured by the deployment team on a machine where developers do not have access and the config file values for connection strings are changed to match the production environment.

Answer 2

We do somthing similar using database deployment. Each enviroment has a configuration database, and every package references a single xml config file in a common file path on every server/workstation, e.g., "c:\SSISConfig". This xml config file has one entry that points to the appropriate config database for that environment. All of the rest of the SSIS configs are stored in that config database. The config database in production is only accessible by the admin group, the developers do not have access. When new packages and configurations are deployed to prod, connection strings are updated by the admin group. The packages are all set to "Dont save sensitive".