How to force SSIS (dtexec) to use an alternative configuration file?

Question

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

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

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

Answer 1

I do not have a link to an article that documents this behavior, but I confirmed it. If the file specified as the configuration file in the package configurations is available at run time, it will be used in the preference specified on the command line.

In my experience and my opinion, this is contrary to normal behavior when specifying a command in a command should override the built-in default value.

To use the configuration file specified in the dtexec command, rename or delete the file specified in the configuration line of the XML configuration file in the package configuration organizer.

Answer 2

I am using SQL Server 2008 R2, and I was getting the same problem as the configuration override seems to be ignored. I found the trick I needed was to remove the XML configuration setting from the package (Package Configurations), and then when you start the package, the XML configuration file you specified will be indicated. However, there are no messages about the use of the file (and since you removed the XML configuration definition from the package, this message is also not issued).

MSDN has an explanation (see the section "Understanding how SSIS package configurations are applied at run time"), which at first did not make sense to me, but after it turned out that the absence of an XML configuration file gives the desired result, I can see what he is trying to say.

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

• "The utility applies the configurations that were specified in the package at design time and in the order specified in the package." Thus, the value of DEVServer is loaded from the package.
• "Then the utility applies any parameters that you specified on the command line." The value in my XML file (TESTSvr) is now loaded. I can provide any file name that I like here and it will be downloaded (be it France or Italy).
• "Then the utility reloads the configurations that were specified in the package during development and in the order specified in the package .... The utility uses any command line parameters that were specified to reload the configurations." Pay attention to the second part of the rule using command line values. Since we have currently installed the server in TESTSvr, this value is now used to load other configuration values ​​from the [SSIS Configurations] table that you want.

Answer 3

Found a way!

In the designer, simply clear the "Enable package configurations" checkbox in the SSIS → Package Configurations section and save.

dtexec will still respect the supplied configuration file in the / conf switch, but it will no longer attempt to use the development-time configuration file, even if it is available.

I still agree that this is a weird behavior, and that / conf should override the development time settings no matter what.

Answer 4

This should work

  /CONFIGFILE "C:\Italy.dtsConfig" /REPORTING V 

Specify the full location of the configuration file in double quotes

Edit:

When you deployed your package to MSDB, then the command to execute the package

  DTEXEC /SQL "\Package.dtsx" /SERVER "Server Name" /CONFIGFILE "C:\Italy.dtsConfig" /REPORTING V 

If you deployed to the file system, then

  DTEXEC /f "Physical Package Location" /CONFIGFILE "C:\Italy.dtsConfig" /REPORTING V 

Check if you point to the correct package

MSDN

  You can use the /ConfigFile option to load additional configurations at run time that you did not specify at design time. However, you cannot use the /ConfigFile option to replace configured values that you also specified at design time