Config files specified in SQL Agent being overridden by design time package configuration

In work, our DEV and our UAT DB environments reside on the same box, but with their own SQL instances. We prefer to store SSIS packages on disk as they are a little (actually a lot) easier to manage that way. Since our DBs are on the same box we could, when configuring SSIS packages just copy the packages to two separate file locations for DEV and UAT and alter the design time package configuration to point to DEV.dtsconfig for one and UAT.dtsconfig for the other.

Rather than this however we attempted to just have the SSIS package in one location and override the default .dtsconfig file which was defined at design time by passing in the the environment specific .dtsconfig as part of the two (DEV & UAT) SQL agent jobs set-up to execute the package on schedule.  This can be done from the configurations tab in the job properties.

overiding-design-time-config

 

We expected the package to take its settings from UAT.dtsConfig as that was what was defined in the job but as we found this was not the case. This is because as of SQL 2008 SSIS packages load configurations in the following order:

  1. The utility first applies the design-time configurations.
  2. The utility then applies the run-time options that you specified on the command line when you started the utility.
  3. Finally, the utility reloads and reapplies the design-time configurations.

which meant the .dtsConfig specified in design time configuration was used. According to Behaviour Changes to Integration Services Features in SQL Server 2008 R2 on the MSDN site one can use /set to change design time settings but not the location of settings.

To get the .dtsConfig specified in the job to be the effective one, we needed to disable package configurations in design time. Not delete the existing one, but just disable it. After that the config file specified in the SQL Agent job was used.

Relative path to config file in BIDS/SSIS 2008 package configurations

As we know using absolute paths in our code can complicate things from a deployment point of view, so it’s best to use relative paths were possible. In business intelligence studio 2008 however the package configuration wizard doesn’t allow you to enter relative paths when pointing to configuration files. If you attempt to type a relative path in, clicking next will replace it with the absolute path, so we appear to have a problem here.

package-configuration

There is an easy work around however, rather than using the GUI just edit the .dtsx file directly to point to the relative path. For example if in BIDS package configuration window you have entered the path as ‘c:\SSIS\active.dtsconfig’, simply change that to ‘..\SSIS\active.dtsconfig’ using a text editor and next time you open the wizard the relative path will be used.