Post reply

one Config File for Multiple dtsx files

  • April 27, 2009 at 1:29 am

    #133542

    Hi,

    My SSIS project is having 12 dtsx files and all variables(ConnectionString, LandindDirpath, ...) are saved in 12 config Files(xml)... each dtsx is mapped to corresponding config files...

    Here ConnectionString Variable is common for all dtsx packages. So Can I create single config file for conectionstring variable alone...

    Because When I deploy in someother env., I need to open 12 config files and replace connectionstring value by the corresponding servername,username and password.. If I hav a one config file for connection string variable alone, I can chagne in that xml alone...

    can anyone pls suggest how to do this..

  • April 27, 2009 at 7:28 am

    #984334

    Each package can read variables from multiple XML config files. Under the Package Configurations Organizer window, you can add a reference to each package to an XML file that contains only the connection if you like, in addition to the XML files that each contain package-specific variables.

    Another option (that I tend to use) is to create a parent package that acts as a command and control module for the processing packages, and passes its variables to the others in a parent to child package relationship. In this case, the common connection string would be in the parent package config file only.

  • April 27, 2009 at 11:50 am

    #984543

    Thank you so much Dave ..

    Can you please put more light on this Parent Child packages thing..

    AS the packages are already developed , does it require to develop the packages from the scratch..? Can you please advice...

  • April 27, 2009 at 12:17 pm

    #984565

    Adding a parent package to a solution doesn't require re-writing packages to make them children packages. A new package, parent.dtsx, for example, can be added. This would consist of a control flow which would have an 'Execute Package' task for each of the child packages that will be run, either serially or in parallel. A user variable in this parent package would be added to hold the connection string. This is then configured via an XML file for the parent package. Within the child packages, a 'Parent Package Variable' configuration is added, with the Connection Manager set as the target object.

    I've added a graphic that shows how the child package configuration looks in one of my solutions.

  • April 28, 2009 at 3:37 am

    #984933

    Great Thanks!!! Dave.

  • February 29, 2012 at 2:10 am

    #1452981

    Thanks me to got the solution for my problem...

  • February 29, 2012 at 2:43 am

    #1452996

    one approach would be to store the connection string as an entry in the registry so that all new packages can also use them. you can also share a single config file among multiple dtsx palckages

    Jayanth Kurup[/url]

  • February 29, 2012 at 9:26 am

    #1453240

    PErsonally I really hate using the registry for this, along with environmental variables. They are harder to set and just as easy to miss. If you have a naming standard for connections then a config file can be re-used easily for the same connection. It works great. Had a solution with 14 packages and 5 different connection config files (some packages used them all, others only 1 or 2). It made it really easy to re-use them and when I needed to change a connection I could do it in one place.

    CEWII

  • February 29, 2012 at 9:49 am

    #1453259

    Just my two cents but I much prefer to use the SQL Server Config files rather than XML

    I find it much easier to maintain and make changes if necessary 🙂

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • February 29, 2012 at 11:56 pm

    #1453460

    to each his own 😀 . I had always used a config file to store all my variables and connections strings. But recently I have shifted to using the registry because i feel its more secure.

    Jayanth Kurup[/url]

  • March 1, 2012 at 7:55 am

    #1453637

    I dislike the registry for configurations. It is less visible and my experience has been it is easier to miss when deploying. So perhaps it is more secure in a "security through obscurity" sort of way. But I don't recommend using it.

    CEWII

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply