SSIS : indirect configuration and one configuration per connection in a configuration database

In this post i'll discuss an example with a package configuration in integration services with an indirect configuration (environment variable) to a configuration database. For every connection a reference in a configuration table in a configuration database is created and for every connection a reference is created in the package configuration. Ray Barley blogged about this on MSSQLTIPS. 

So when you create a connection is created, there are three steps: 1. create the connection, 2. create the package configuration and 3. Create a reference in de configurations table.

The configurationtable can be divided in different sections: Master configurations, Database connections and project configurations. In the master configurations reside the master definitions like a rootfolder for all of your SSIS projects. In the database connections section every connection is stored once and in the project configurations section project variables could be stored.

 Advantage 

• Flexible.
• Strong cohesion in the SSIS package and less with his environment.
• Maintainable.
• Secure. 

 Disadvantage

• You need a (master or per project) configuration database.
• Not very easy portable. You need to create scripts to port from one environment to another. 

Greetz
Hennie