Configuration Changes in SSIS 2008

In a previous post, I detailed some of the challenges that I had encountered when trying to modify the connection string used for SQL Server configurations from the command line. In SSIS 2005, command line options were applied after the configurations were loaded (with the exception of parent package variables). Effectively, this meant that you could not change the location a configuration pointed to from the command line.

This has been changed in SSIS 2008. Configurations are now loaded twice, once when the package is originally loaded, and then again after any command line options are applied. The order of events is:

1. The package file is loaded.
2. The configurations specified in the package at design time are applied in the order specified in the package (except for Parent Package Variables).
3. Command line values are applied.
4. The configurations specified in the package at design time are reloaded in the order specified in the package (except for Parent Package Variables). The configurations will use any updated values from the command line when they are reloaded, so it is possible to change the connection string for a SQL Server configuration.
5. Parent Package Variable Configurations are applied.
6. The package is run.

This is a nice improvement, since you can now set up a package with SQL Server configurations, and point the package to the correct database are runtime by simply using the /CONN switch of DTEXEC. However, it still isn’t perfect . Since the design time configuration is applied twice, a value that you apply using /SET on the command line can be overridden by a preexisting configuration.

If you’d like to see this behavior, I’ve provided a simple package. The package has a single variable (“TestVar”) defined. It has an XML configuration enabled that sets the value of TestVar to “Dev”. There is a single Script Task that fires an information event that contains the value of the variable.

public void Main()

        {

            bool fireAgain = false;

            Variables vars = null;

            Dts.VariableDispenser.LockOneForRead("TestVar", ref vars);

            Dts.Events.FireInformation(0, "", "Value is: " + vars["TestVar"].Value, "", 0, ref fireAgain);

            vars.Unlock();

            Dts.TaskResult = (int)ScriptResults.Success;

        }

If I run this package using this command:

dtexec /file Configuration_Overwrite.dtsx /SET Package.Variables[User::TestVar].Properties[Value];Prod /REP EWI

I’d expect to see the value “Prod” reported in the output. Instead, “Dev” is displayed. If you disable the package configurations and re-run it using the same command, it does report “Prod”.

Overall, I prefer the new behavior, but it does introduce a new issue to be aware of. In general, you need to be careful to not /SET a value that is also specified in a configuration, as it will be overwritten. I’ve filed a Connect suggestion for the addition of /SetBefore and /SetAfter switches to DTEXEC, so that you can explicitly define when you want the /SET applied. If you think it’s a good idea, please vote for it.

The sample package is on my SkyDrive. You may need to update the location of the XML configuration, but it has no other dependencies.

Thanks to Cho Yeung on the SSIS team for clarifying the load order when the package is executed.