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.

3 Comments

  1. amir says:

    At step 4 of Package loading order it has been described “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.” But example shows it is not over written..
    Is it a mistake or I am not able to understand correctly?

    • jwelch says:

      The examples shows that the variable value is overwritten twice – once by the value specified on the command line, and once by the value specified in the configuration. It works fine as long as the value you are setting doesn’t also appear in the configuration.

  2. Dex says:

    To compound ontop of this, if you develop your 2008 SSIS package locally and it has config files enabled then you deploy to the SSIS server (via filesystem) if the server as well has a copy of the the SSIS package in the same directory structure (say because you pulled it down from TFS to debug an issue). The local server’s config file(s) are actually what gets loaded which causes nightmareish headaches to track down. This can be checked by setting connection strings on your local config files to use a different provider than the server’s config files, then once your local copy of the SSIS package has been deployed to the server if you open up the dtsx file on the server in BIDS, you will see the config file pickes up the server’s config files. Try tracking this down when your package is called from a SQL Job Step and wondering why you can’t get the correct variables loaded…..

Leave a Reply