November 19, 2010 at 12:20 pm
I have a SSIS package that basically transfers data from SERVER1 to SERVER2 (both are production machines). I now need to setup a development environment to test these packages beforehand. Any ideas on how this could be done without having to change the data connections within the package?
When I test, I want data to go from SERVER1 to SERVER2DEV. However, if I promote the package to my production machine, then the data should flow from SERVER1 to SERVER2.
Package consists of multiple data flows and SQL tasks (to clear data beforehand). I hope someone has had a similar experience and a better solution as constantly changing my connections leads to a greater possiblilty of promoting a change with the wrong servers.
Thanks
November 19, 2010 at 12:46 pm
Configuration files are your friend. You set up a config file with the necessary configuration items in it for each environment you need to run under. When you run the package just pass the proper config file to it and the package will adopt those settings.
When you create your config file, through SSIS>Package configurations, you can enable package configs, and create the config, which contains the settings you want to be able to change for the package. Note that you will have to do some drilling down in the possible options, but most likely you will just use the ConnectString for each of your data connections.
If you only have two environments you may only need one config file, for Prod. you set the connections in your package to the Dev settings and run the package without a config file while developing/testing, then in Prod you use the config file set up for Prod.
Once you have created the initial config file, you can modify it as necessary in your choice of XML editor. You can make copied if it and change the parameters in each copy to suit each environment you have.
Another thing is, once you create your initial config file, you can remove it from the list of configs in the Package Configuration Organizer, so that the package doesn't automatically try to use it every time it runs. The package will still accept an argument of the package config, but it won't look for one in particular. You do need to leave "Enable Package configurations" checked though.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
November 19, 2010 at 12:52 pm
Thanks for the quick and detailed response. Sounds exactly like what I need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply