Is there a way to easily change the server name on several SSIS packages programmatically?

Question

We are creating several SSIS packages to migrate a large database as part of a release cycle. We may end up with about 5-10 SSIS packages.

As we have 4 environments (dev, QA, staging, production, etc.), is there an efficient way to change the destination server for each SSIS package as they go through the different server environments? Ideally, there could be a script that is run that would take as a parameter the server that was needed.

Answer 1

You could use a configuration file to store the connection strings for the servers. Then as you moved from environment to environment, you would simply change the config file. To simply create a config file, on the control surface of your package,
1) right click and choose Package Configurations from the context menu.
2) Check the box for Enable package configurations if it is not already selected,
3) then Click the Add... button.
4) Click next on the dialog,
5) then add a Configuration file name: and click next.
6) In the Objects View, Under Connection Managers, expand your connection, then expand Properties and check the box next to ConnectionString.
7) Then click next
8) then finish.

You now have an xml file named what you named it in step 5 above. You can edit this file with a text editor and change the connection string to map to whichever server you need it to before each run.

Once created you can share the config file between multiple packages as long as the objects referenced are named the same between the packages.

This is a rudimentary tutorial on configurations, there are many ways of saving configurations of which this is only one. For more information on configurations consult your favorite SSIS book

Answer 2

We use a config table that stores the configurations for the server. But config files work well too. We like the table because we are doing reporting on SSIS package meta data and it's easier to grab this data (along with a lot of other data we store as well) when stored in a table.

Answer 3

William Todd Salzman's answer covers most points. I have a couple more to add:

• Make sure the pacakge ProtectionLevel property is DontSaveSensitive
• If you are working with different shipping environments, then a SQL Server table as a source for the package configurations is maybe not for you, as you will require one central database containing all the connection strings for all the servers.
• Having worked with package configurations retrieved from the registry, you will need to be aware that these settings are retrieved from the HKEY_CURRENT_USER hive. This has implications for when the package is run through a SQL Agent Job.