Saturday 30 October 2010

Using Single Configuration file for all Packages


Update : 03 July 3, 2012 : you need to do delay validation=true in all the packages to make this thing work.


In many case we have different connections in our packages and then we end up doing 2 or more configuration file for a projects.
This is way around by which you can have all configuration string in to one single package say in master and then pass this string to child packages.
Following are steps for it
1.       Keep all connection strings in SSIS variables in you master packages.





      


2.  To pass this connection string to child packages use parent package variable settings of SSIS.








































3.       Go to Package configuration of child package and click on add new configuration. Select configuration type as Parent Package variable. Give the parent variable name which is storing connection string. Press next to continue


4.       It will ask for a target property to select. Please scroll throw it and select your connection’s property connection string



5.       Press next, give this configuration a name and then press finish.
And we done here. Now whenever you change the connection string in the variable and run packages with the help of master it will load new connection string,



4 comments:

  1. sorry dude...but the package will be validated first and then it process all this..so it will fail right

    ReplyDelete
  2. nope..i have tested this approach...just to a delay validation = TRUE.

    ReplyDelete
  3. To add to this - It requires the delay validation on the child package Connection.

    ReplyDelete
  4. could you provide the connection string

    ReplyDelete