January 27, 2010 at 2:02 pm
Hello,
We have around 200 SSIS packages that we developed recently. Since all these packages need to be moved to production via 3 other test servers, we setup environment variable that stores the path of the config file and the same is used in all the packages. Also to mention that each server host more than 5 database instances used by different applications. We need to understand if the use of environment variable can cause any type of issue with any of the instances running in the same server. What's the best practice I can adapt here so that I don't have to edit the package everytime I promote a package to prod.
January 28, 2010 at 12:10 am
I use a multi-tier approach. Basically, all my configuration settings are stored in a sql server database. To point my packages to this database, I use an indirect configuration. This looks as follows:
environment variable points to xml configuration file
xml configuration file contains the connection string to sql server database
sql server database contains all other configuration settings
Now you can change all settings (location of the xml file, connection string to the database, individual configurations) on every server without any need to modify your packages. You can have of course multiple environment variables and xml configuration files on 1 server. Just pick the right environment variable in each package.
You'll find a good description of this approach in Microsoft SQL Server 2008 Integration Services Problem-Design-Solution starting from page 37.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply