Home > SSIS Best Practices > SSIS #106 – You can create package configuration files to make SSIS package dynamic and portable

SSIS #106 – You can create package configuration files to make SSIS package dynamic and portable

I had many posts that share my experiences with using package configuration files to make my SSIS packages portable from my local development BIDS environment, to the test environment, and the production environment.

In the next few blogs, I’ll share with you a few simple steps to create your own XML configuration files and then how to use them to make your SSIS packages dynamic and portable.

Benefit of using package XML configuration files

There are many resources on this topic. In a nutshell, using package configuration files is a powerful way to make your SSIS package extremely versatile.

Example 1 – Make a connection string for a connection manager dynamic

We typically have our server name, database name, user name (password as well sometimes) embedded in our Connection Manager.

When we are ready to move our packages from development to test, or to production, the least we would expect is that the server name will be different.

In this case, the goal of using a package configuration file is:

1) no modification to our packages themselves

2) the only change we will make is to the package configuration file. I’ll need to change the Data Source and the Initial Catalog accordingly.

image

Example 2 – Make a Value for a variable dynamic

A typical example is a path where we fetch our incoming files. This file path will be most likely different from what is on our local PC and what is on our test or production server, especially the root folder will be most likely different.

Again the goal of using a  a package configuration file in this case is:

1) no modification to our packages themselves

2) the only change we will make is to the package configuration file. I’ll need to change the ConfiguredValue accordingly when I move my package to the test or the production server.

image

So, it’s fair to summarize the benefits of using package configuration files as:

  • no modification to our packages themselves
  • the only change we will make is to the package configuration file

     

  • If it’s your first time to use a package configuration files, and you received it from a co-worker, you might wonder how the package configuration file is created.I’ll share two examples with you. One example is to create a package configuration file for a connection manager. Another example is for a variable.The steps of creating package configuration file is simple enough. There are two things that are not crystal clear though:1) The multiple choices of properties that are available to us. All the properties are exposed to us. Which one do we need? 

image

     

    2) What are we going to do about the configuration file?

    I’ll continue to share with you these two tasks when I get time in the future.

Categories: SSIS Best Practices
  1. aleks
    January 17, 2012 at 9:40 am

    Hi Sherry,

    How do you manage package configuration in case you have embeded packages:
    MainPkg -> call SubPkg

    Do you use *.dtsConf in both packages as xml configuration, either xml configuration in MainPkg and ParenPackageVariables in SubPkgs?

    In first case we will get “This error is thrown by Connections collection when the specific connection element is not found” in case new connection will be added to on of packages and dtsConfig.. So the solution will be to add same connection to all packages that are called wthin a flow… but it could be annoing if we have many of them ..

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: