Today I was struggling with package configurations in SSIS. I wanted to use a Sql Server store to configure an SSIS package, but I needed the configuration filter to be dynamic. A colleague pointed me in the right direction: use the indirect 'environment variable' technique in the Configuration Package manager.
In SSIS it is possible to store configuration values in an Xml file, environment variable or Sql Server table. Every time your package is executed, the values are loaded first (e.g. into your user-variables). With the SqlServer store, it is also possible to use the same table in a database to store configuration data for more than one package by using a Configuration Filter to select the right records. It's a bit counter-intuitive in the UI for creating package configurations though, so I thought I'd post a short howto.
1. Create a (direct) Sql Server package configuration from the SSIS > Package Configurations... menu.
2. 'Add' a configuration, and select Sql Server.
3. Select a database and create a table with the 'New' button.
4. Fill in a key for the Configuration Filter value (it doesn't matter what).
5. Now click 'Next' and select the variables you want to store in the configuration. (Note: notice how you can click the '+' sign in front of a user variable to deselect stuff that you don't need. In most cases you just need the value of the variable and not wether it was changed or not...).
6. Finish and you have a new configuration. Yay!
And now for the neat trick: create another configuration.
7. Choose Sql Server this time as well, but select the 'Configuration location is stored in an environment variable' option.
8. Fill in a name for the environment variable and click Next till you finish.
On the over view screen you now have 2 package configurations. A (direct) Sql Server config and an indirect sql server config.
Next you need to create an environment variable with the right content to point SSIS to your configuration package. (Right-click on 'My Computer' > Properties > Advanced > Environment Variables). Now take a good look at the package configurations list in the screen you had open before. In the entry for the (direct) SqlServer config, there is a column 'Configuration String'. This is the value you need to use for your environment variable. It is something like "SSIS_CONNECTION_MANAGER_NAME";"TABLE_NAME";"CONFIGURATION_FILTER".
By changing the CONFIGURATION_FILTER part of the environment variable's value, you can now point different packages (or different instances of the same package) to different configuration values.
The obvious next step is to run DTexec.exe and change the value of the environment variable every time.
Why oh why do you need to create the direct one first, you may ask? Well, this way it will create the rows for you in the database for all the variables you select, and you can simply copy them with a new ConfigurationFilter value. If you create an indirect one from the start, you need to create the table and add the rows yourself. It helps to use the direct way until your set of variables is pretty stable, that way you benefit from the automatic adding of the variables to the table.
Thanks for your help, Robert!