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.
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
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.
William Todd Salzman's answer covers most points. I have a couple more to add:
