zondag 17 oktober 2010

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

Geen opmerkingen:

Een reactie plaatsen