SSIS configuration precedence

There are a number of ways you can modify values within an SSIS package. The most basic would entail editing the package each time to set the value. You scoff at the idea but I know it happens out there. Slightly less bad is what I ran in to at my current assignment. They had copy and pasted a script task across all their packages that opened up machine.config and assigned values based on keys. I give them props for at least not hardcoding their work but as those wacky kids on the Internet say, "you're doing it wrong."

A more standard approach would be to either use the built in Configuration ability of SSIS or to pass the values in at run-time via the /Set mechanism of dtexec or by using the Set Values tab from DTexecUI (Execute Package Utility). In the MS SSIS forums, I often suggest people use the set value approach because they usually haven't designed their package, not that it takes much, to use the configuration approach. However, I had not actually used the set value option on a package before. It worked well, once I figured out how to use it. The biggest pain is determining the property path for the item you want to configure. For example, I created a package level variable called Counter as an integer. The help for dtexec says
the format is */SET PropertyPath;Value* However, to configure my variable, it would look like the following

dtexec /file ./Order.dtsx /Set \Package.Variables[User::Counter].Properties[Value];3

The other thing I want to examine is the order in which values are applied. My current example has the default value of 0, an XML configuration file that sets it to 1 and a run-time override of 3. So far, it appears that the run-time override always trumps other configurations. If that always holds true, I can see that being useful. I could have sworn I had seen someone discuss this prioritization before but I have not been able to find that post.

http://cid-84dfa4294693ec43.skydrive.live.com/self.aspx/SSISForums/Order.dtsx
and the config file is
http://cid-84dfa4294693ec43.skydrive.live.com/self.aspx/SSISForums/Configuration.dtsConfig
need to zip them and polish this post

2008 Caveat

This behaviour changes http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=4214500&SiteID=1

Edit - 2011-10-03

John Welch has a superior post on Defining a Configuration Approach for Integration Services Packages. In particular, he has a nice, succinct break down of when they are applied by version.

SQL Server 2005 Integration Services

When you execute the package in SQL Server 2005, configurations are applied in this order:

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. Any options specified from the command line are applied. Any configurations specified on the command line overwrite the current values; however, they do not trigger a reload of the configurations if a changed value impacts a configuration dependency. For example, if the connection string used for SQL Server configurations is updated from the command line at run time, the package will still retain any other values from the design-time SQL Server configuration database.

4. Parent Package Variable Configurations are applied.

5. The package is run.

This order can create a scenario that is difficult to resolve with Integration Services configurations. If you use XML - based configurations, you need to specify the path to the configuration file. If you are using SQL Server configurations, you need to specify the initial connection string to the database that holds the configuration table. Ideally, you would be able to override these values by using the command line switches of DTEXEC. However, since configurations are applied before values specified on the command line, this does not work. For example, if you specified a new connection string for the configuration database at the command line, the SQL Server configurations would be applied from the original connection string. Then the connection string from the command line would be applied. However, the SQL Server configurations would not reload, so there is effectively no way to override the configurations from the command line.
SQL Server 2008 Integration Services

This behavior has been changed in SQL Server 2008. Configurations are applied twice - once before the command line options, and then again after applying the command line options. The configurations are applied in this order:

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 an improvement over the behavior in SQL Server 2005. However, there is still a potential problem with the new functionality. Since the design time configuration is applied twice, it can overwrite a value specified on the command line. This means that you cannot override a configured value from the command line without also overriding the design time configuration location