Phidiax Tech Blog

When setting up a SQL Server Integration Services Package to be run by a SQL Agent Job from the File System, you may run into a less-than-expected issue in running the package with a new configuration.

Package Setup

To demonstrate, I have created a simple package where a variable is written to a table. 

Let's say you setup this package with Configurations enabled to store out this variable, and name the configuration file "F:\DtsConfig\NewPackageConfig.dtsConfig". 

Once you complete development, you create a new configuration file for your production environment and name it "F:\DtsConfig\NewPackageConfig-PRODUCTION.dtsConfig" and drop both configuration files and the package file on the SQL Server. 

SQL Agent Job Setup

When you schedule the job with SQL Agent to run the package, you specify the PRODUCTION configuration file on the step in the agent job. 

So running that job should use the PRODUCTION configuration file, right? 

---NOPE!  WHY?---

The SSIS Configuration system actually will attempt to use the configuration file named during development first (which happens to exist in this scenario), and use the configuration specified in the job if that original file cannot be found. 

The Fix

Many blogs out there mention that you need to edit the package in BIDS to remove the configuration in it, which isn't ideal for future development where it may be necessary to make configuration adjustments. In reality, removing or renaming the configuration file specified during development from the SQL Server filesystem will suffice.

Now, the test is successful and the specified configuration is used: