Processing Order of SSIS package configuration files
by Hans on 2009/11/12 09:26
in Categories: SSIS

When SSIS was first revealed it saw many of the flaws of DTS being sorted out. One of these flaws was the lack of the ability to specify database connection string properties, file and folder locations and names without modifying the DTS package.

This was one of the first features I endeavoured to try out and test. Woe is me, it was not to be without frustration.

I created my SSIS package added a text file source a table destination in a database to load some data. Then I added the configuration file and all seemed to be working just dandy. The wizards where a great help.

The problem surfaced when I setup a SQL job and added my "Production" configuration file as part of the job step configuration. I modified the production config file to source data from a different text file and point to a different SQL server. Press the run button and guess what ? ... my production config file was completely ignored !

What followed was an hour of Google and code changes until I finally figured out what was happening. It is really important to understand the order in which the SSIS package loads and applies properties and related values and know the gotchas.

When your SSIS package runs it loads the design time config and values it then looks for any expected config files. If it does not find these config files it will use the design time values. If it finds the config files generated during design time it will load and use these properties and values. If you have more than one config file that specify the same properties the values of the last config file loaded will be used. Note you can specify the order in which the config files must be loaded.

So that’s all great while you are in design time what about production? The assumption here is that you have copied your DTSX file to the prod server added your SQL job and related job step. Now, if you don’t specify any config files in the job step and the design time specified config files do not exist the SSIS package will load and use the design time values. If it finds any of the design time specified configuration files the values specified in these files will be used.

If you have specified a config file as part of your job step and the SSIS package finds any config files specified at design time, hold your breath here it comes, your config file specified in the job step will ignored! To ensure the config file that you specified as part of your job step is used the config files specified at design time must not exist on the local server.

There is some documentation on MSDN that describes how package configs are applied at run time that’s pretty good but unfortunately it’s those GOTCHAS that they don’t cover.

ok folk that’s all from me for the moment, hoping this will be of some value to you.

Cheers and happy hacking from the Saxon team.

Post a Comment

Your Name:*  OR Screen Name:
Email Address:*
Post your Comment

    Note. All comments are sent to a moderator for approval prior to appearing on this page.

    © 2012 Saxon Systems. All rights reserved.Terms of Use  Privacy Policy  Contacts