Now in SQL 2012 we have parameters that make it easy, but configuration files are still an option and I still see a lot of my clients using them even on 2012 due to several reasons, but mostly because of the work to convert over.

SSIS packages are great ETL tools and can do just about anything you need in terms of ETL. Most organizations start out creating SSIS package one by one until they have dozens, hundreds, or even thousands of packages. I have worked with one client that ran over 4,000 packages. This can be a nightmare to maintain. You can save yourself a lot of work by deciding upfront how to configure your packages using configuration files or tables. We are going to discuss configuration files in this article.

We are going to look at a simple example of passing information to a package with a configuration file. Then we will go over using configuration files on multiple packages. Imagine running dozens of packages that point to a server and the server name changes. If you have a configuration file that is feeding this server name to every package you can make a single change to the configuration file and all the packages are updated. This can reduce your maintenance time significantly.

Here is a simple package example:

1. Drag in a script task into a blank SSIS package.

2. Create a string variable on the package named strData

3. Set the value of the variable to “Package”

4. Double click on the script task.

5. Add the strData variable to the read only variables.

6. Click Edit Script

7. Under the main function add the code MsgBox(Dts.Variables(“strData”).Value)

8. Click save and then close the window

9. Close the script editor by clicking ok

10. Run the package

When you run the package a popup box appears show the work Package. This is the value of the variable saved in the package. Now we will set up a configuration file on the package to give us the ability to change the value of the variable from outside the package.

1. Close the popup box and stop the package.

2. Right click in the control flow and select Package Configurations.

3. Place a check in Enable Package Configurations.

4. Click Add.

5. Click Next in the in the welcome screen if it appears.

6. Click on browse and select a location you have rights to write to.

7. Name the file FirstConfig

8. Click save and then click next

9. Click the plus next to variables >strData > Properties

10. Place a check next to value, notice the value on the right

11. Click Next > Finish > Close

We now have a configuration file on the package but the value is still the value from the package. Now we will open the configuration file and change the value. The configuration file is an XML file and I like to use XML notepad (Free from Microsoft) to open it. You will look for the configured value in this file. This configured value is the value passed to the package.

1. Open the folder containing the configuration file

2. Open the configuration file by right clicking and select open with

3. Select a program you can use to edit the file (Example: Notepad, Wordpad, XML notepad)

4. Check the configured value from Package to Config

5. Save and Close the File

6. Return to the package and run it

You should see a popup showing Config. This is the value from the configuration file. The value saved in the package is overwritten at run time.

This is just one small example of using configuration files. A popular way to use configuration files is on connections. When you have a connection on a package the properties of this connection show in the configuration manager. You can place a check next to the connection string property or you can place a check next to the individual elements that make up the connection string, initial catalog, server name, user name, and password. The user name and password are not needed when using windows authentication.

The password is not stored in the configuration file automatically even if you select it in the configuration manager. This is done by design for security. Microsoft did not want you saving your configuration file in plain text without knowing it. So you will have to open the configuration file and add the password. If you selected the connection string the password will go right after the user name. You must type in “Password =####;”, (#### Represents your password). Don’t forget the semicolon after the password. Now this configuration file can be used in any other package using this connection.

There is an issue when using a configuration file in multiple packages. The package that is using the configuration files will try to load every connection in the configuration file. It the package does not contain that connection it will fail validation and the package will not run. This causes an issue when trying to share a configuration file with many packages. There are three methods for handling these issues. You can create a configuration file for each package or create a configuration file for each connection or a combination of both.

The first method of a configuration file for each package works well if you do not have a lot of packages. If you have a thousand connections and fifty packages, a per-package solution is the obvious choice. If every package has a different set of connections this is almost necessary.

The second method of a configuration file for each connection works well if you have a lot of packages and fewer connections. If you have fifty connection and a thousand packages it will be much easier to maintain a per connection solution. In this situation a package with ten connections would have ten configuration files, each with one connection.

The third option is to combine the first two options in some form. For example, if you have one connection that is used by every package use this configuration file in every package. The other connections can have a package level configuration file. This is harder to maintain and you need document which packages are using which configuration files.

With all the options of configuration files it is important to plan out how you will use them in your environment before you create thousands of packages and create a maintenance nightmare. Planning your SSIS package configuration architecture is important and should not be over looked. It is easy to put it off when you only have a couple of packages. Most environments have their packages grow in number faster than anticipated. Planning your configuration files will save you a huge retro fit project in the future.