How do I make SSIS (dtexec) use an alternate config file?

Question

I've configured my SSIS configuration to load from an XML file. When I run the package with dtexec, I specify a different configuration file for each country I'm processing. In Visual Studio I specified this as France.dtsConfig (I have to choose one and this was the first one).

When I run the package with dtexec /FILE Import.dtsx /Reporting V /ConfigFile "C:\Italy.dtsConfig" I still see the output telling me that "The package is attempting to configure from the XML file France.dtsConfig".

I thought I could override the configuration by providing a different dtsConfig file for each country. Is this possible? What am I doing wrong?

Answer 1

I am using SQL Server 2008 R2 and I was getting the same issue with the Configuration override apparently being ignored. I found the trick I needed was to remove the XML config setting from the package (Package Configurations), and then when running the package the XML configuration file you specify is applied. There is however no message emitted about using the file (and since you removed the XML configuration definition from the package, that message is also not emitted).

MSDN has an explanation (go to section "Understanding How SSIS Package Configurations Are Applied at Run Time") that at first didn't make sense to me, but after finding that not having an XML configuration file defined gives the desired result, I can see what it is trying to say.

In my case I was using the XML file to set the instance name of the server on which the [SSIS Configurations] table was found. At design time this was DEVServer in the connection manager object, and I want to override the value to TESTSvr. Following the rules:

1. "The utility applies the configurations that were specified in the package at design time and in the order that is specified in the package." So the value DEVServer is loaded from the package.
2. "The utility then applies any options that you specified on the command line." The value in my XML file (TESTSvr) is now loaded. I can supply any filename I like here, and it will be loaded (be it France or Italy).
3. "The utility then reloads the configurations that were specified in the package at design time and in the order specified in the package. ... The utility uses any command-line options that were specified to reload the configurations." Note the second part of the rule, about using the command line values. Since we currently have set the server to TESTSvr, this value is now used to load the other configuration values from the [SSIS Configurations] table that you want.

Answer 2

I don't have a reference to an article that documents this behaviour, but I have confirmed it. If the file specified as the configuration file in the package configurations is available at run time, it will be used in preference to the one specified on the command line.

In my experience and my opinion, this is contrary to normal behaviour where specifying something in a command should override the built-in default.

To use the configuration file specified in the dtexec command, rename or delete the file that is specified in the Configuration String of the XML configuration file in the Package Configurations Organiser.