SSIS Package uses incorrect Package Configuration Settings

SSIS packages evaluate configurations (XML at least) in the order of:

1) What they’re told to look for (say in a job or manual execute step)
2) The path saved in the package: Package Configuration
3) Default values in the package itself: Hard code value

So it makes sense that when #1 is not available, it defaults to #2, and when #2 is also not available, it defaults to #3

On occasion we have a package that insists on using the hard code values instead of actually using the designated config file. When that happens, we have to open the package, strip out the hard coded values, setting properties to not evaluate upon open (DelayValidation=True), and disconnect the local config file settings (just remove the config file name in the Package Configuration). This is a good work around.

Package Configurations

SQL Server Integration Services provides package configurations that you can use to update the values of properties at run time

Configurations are available for the package deployment model.

Parameters are used in place of configurations for the project deployment model

A configuration is a property/value pair that you add to a completed package. Typically, you create a package set properties on the package objects during package development, and then add the configuration to the package. When the package runs, it gets the new values of the property from the configuration

Understanding How Package Configurations Are Applied at Run Time

When you use the dtexec command prompt utility (dtexec.exe) to run a deployed package, the utility applies package configurations twice. The utility applies configurations both before and after it applies the options that you specified on command line.

As the utility loads and runs the package, events occur in the following order:

1. The dtexec utility loads the package.
2. Apply the configurations that were specified in the package at design time and in the order that is specified in the package. (except the Parent Package Variables configurations)
3. Apply any options that you specified on the command line
4. Reloads the configurations that were specified in the package at design time and in the order specified in the package. (except the Parent Package Variables configurations). The utility uses any command-line options that were specified to reload the configurations. Therefore, different values might be reloaded from a different location
5. Apply the Parent Package Variable configurations.
6. The utility runs the package The way in which the dtexec utility applies configurations affects the following command-line options:

• You can use the /Connection or /Set option at run time to load package configurations from a location other than the location that you specified at design time
• You can use the /ConfigFile option to load additional configurations that you did not specify at design time, but the Package Configuration has to be enabled

  However, these command-line options do have some restrictions:

• You cannot use the /Set or the /Connection option to override single values that are also set by a configuration.
• You cannot use the /ConfigFile option to load configurations that replace the configurations that you specified at design time.

The ConfiguredValue element holds the value to be used in the package for the object specified in the Path element, both open and closed tags must be in the same line, ex:  <ConfiguredValue>abc</ConfiguredValue>