Creating a Solid SSIS ETL Solution: Part 2 – Package Configurations

Creating a Solid SSIS ETL Solution: Navigation
Part 1. – Package and Error Logging
Part 1. – Package and Error Logging (cont.)
Part 2. – Package Configurations
Part 3. – Package Template Overview
Part 4. – Email Task and Package Execution Report

Now that the logging is set up, arguably the most important part of the template, let’s move on to the package configuration set up.

Package Configurations Defined

Before I show you how I use package configurations in my template to ease the process of promotion to production as well as how easy it makes variable and connection maintenance, let’s first look into a little of what package configurations are.

As stated by Microsoft, package configurations are basically parameter variables, as you would use them in a method or object. Each configuration is set up and acts like a parameter, which the environment must feed at run time. This solves quite a few issues an SSIS developer may run in to when developing a package with many moving parts. It allows a package to be flexible, so it may be used for many similar tasks; instead of hardcoding several similar packages to handle one task a piece. You could instead use the one package to do it all by simply feeding the package a different set of parameters at run time.

There are many ways to feed the package the configuration values it needs to operate, such as XML file, via table, environmental variable, and registry entry. Explaining the various sources for configuration information is outside of the scope of this post. More detailed information can be found from MSDN here. My template uses only the environmental variable and SQL table types.

Avoid Rebuilding Packages When Changing Environments

At first package configurations seem to be mainly used to make dynamic packages, but one of the biggest benefits of using them is for changing connection strings and paths to files or archives for the promotion path to the production server. Most professional teams developing SSIS packages for enterprise systems have at least three environments to traverse as they move an information system item from development, to testing, to production. This is typically done to keep errors out of the production environment as well as having strict control over the testing environment and processes. One of the biggest problems associated with moving SSIS packages between these environments is the connections and pathways set up in the connection manager have been set for the development environment, and the only way to change them is to open the package in BIDS or VS, change them by hand, and then rebuild the project. This is a very cumbersome task.

To avoid having to rebuild packages each time they are promoted to the next environment level, best practice is to set up package configurations on these dynamic connections and pathway variables, so we can put some intelligence into each environment and let it set them up as it sees fit. Additionally, if we wish to change connection strings or path variable inputs, we can change them in the environment and it will affect all packages that use the same named configuration. Consider if you had a connection used by 20 different packages. It is much easier to update a record or change an XML file rather than update 20 packages by hand, rebuild them, and retest them. This is further exacerbated when you consider this would have to be done 20 times PER environment! Update 3 records or rebuild 60 packages! Easy choice.

Four Kinds of Configurations

This particular template uses configurations for four different reasons:

1. One environmental variable packages use to connect to the configurations table
2. Connection string configurations packages use to collect the connection strings for the current environment
3. File path configurations packages use to collect the pathways for the current environment
4. Email address configurations packages use to collect who will receive package execution emails in the current environment

Environmental Variable

First we set up an Environmental variable…. Well I actually can’t find a good link which easily explains how this is done (when I did my research, I had to piece it together from a few different articles), so I’ll list the procedure for Windows 7 and Server 2008:

1. Right click ‘My Computer’
2. Select ‘Properties’
3. Click ‘Advanced system settings’ link
4. Click ‘Environment variables…’ button
5. In the ‘System variables’ box, click the ‘New…’ button
6. In the ‘Variable name:’ field, set the name of your environmental variable (SSISConfigDBConnString)
7. Lastly, in the ‘Variable value:’ field, type in the connection string to the database you will use for your configuration table, as the example below shows

Data Source=YourServerName;Initial Catalog=AdminDB;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;

I’m fairly sure using provider SQLNCLI10 will work for SQL 2008, 2005, 2000, 7, and Asure.

Time To See What’s Under the Hood

One thing to know about package configurations is each time a new SQL Server table configuration is created, a row is inserted into the configuration table with the current contents of the connection string or file path variable. For this reason, first you should set up all your OLE DB connections and file path variables with initial pathways.

The environmental variable is in place. Now we can open a package, and set up some configurations.

1. Open a package
2. Right click on the background in the ‘Control Flow’ tab
3. Select ‘Package Configurations…’
4. Click the check box to enable package configurations

The first config we need to set up in the package configuration is the config which will connect our conn string in the environmental variable to the AdminDB connection in the package:

1. Click the ‘Add…’ button
2. In the ‘Configuration Type:’ drop down menu, select ‘Environmental variable’
3. Now in the ‘Environmental variable:’ drop down menu you should see the variable you just set up, select it
4. Click the ‘Next…’ button
5. In the ‘Connection Managers’ folder, expand the tree of the AdminDB connection
6. Select ‘ConnectionString’, and click the ‘Next…’ button
7. Create a name for this config – I try and name all my configs to the same name as what they configure (SSISConfigDBConnString)

Next create configurations for all dynamic connections in the connection manager space. This covers two of the four kinds of configurations in the package template: database connections and file pathways. To do this:

1. Click the ‘Add…’ button
2. In the ‘Configuration Type:’ drop down menu, select ‘SQL Server’
3. In the ‘Connection:’ drop down, select AdminDB
4. If the ‘Configuration table:’ drop down is empty because you haven’t created a config table yet, click ‘New…’. SSIS will show a create table script. I added an IDENTITY surrogate key with PK because I can’t stand heap tables. SSIS will use the ConfigurationFilter col to find the config when a package selects it.
5. Select a configuration table
6. In the ‘Configuration filter:’ field type a name for your configuration (or choose a pre-existing config from the drop down). Remember if you use this name in any configs in other packages, the package will use this configuration, so make sure this configuration name is as unique as need be.
7. Click the ‘Next…’ button
8. For database connections, expand the appropriate connection and select ‘ConnectionString’ – For variables, expand the appropriate variable and properties folder and select ‘Value’
9. Click the ‘Next…’ button
10. Create a name for the config

Repeat for all configurations you wish to set up. For the email configurations, I create one variable for ‘To:’ and one for ‘CC:’, which are used in the email task in my template. I believe there is also a ‘BCC:’ field if you’re so inclined.

Next: Package Overview

Now that the logging system and package configurations are set up, I’ll explain how these two pieces come together as the framework of the template, and how to use this template for your needs. See you on or before Dec. 26th! ==>

Example Files

Files and Test Data