October 30th, 2013, 02:39 PM
Using common conn string in multi-package SSIS Solution
I am taking over an existing data integration for a mid-sized company. My predecessor created about 15 packages for doing various tasks, and each one is in its own solution. I am trying to combine those all into one solution to make maintenance easier.
One of the things he did was hard-code the server names into all of the Connection Managers. This will be a maintenance nuisance (at best) if the server name, DB, or logins change. It will mean changing the values in all of the packages and rebuilding.
I am trying to externalize the connection strings using the Package Configuration functionality. There are about 5 DB servers and a couple FTP servers that are used by the various packages in various combinations. I would like to have one master config file where all those connection values are stored once and used by all the packages.
From what I've seen that doesn't seem possible. It seems that all of the packages have to use their own .dtsConfig file, which I guess makes sense, but makes it difficult to do what I want. It means that I would have to change each config file if one of the values changes.
I know you can specify the config file at runtime using the /CONFIGFILE parameter, but it looks like the package name is one of the values in the DTSConfigurationHeading node, so I don't know that using a common file at runtime is feasible.
Can anyone offer any alternatives, tips, or suggestions?