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.
- Strong cohesion in the SSIS package and less with his environment.
- 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.