SSIS: Creating Package Configurations

This post discusses the creation of Configuration Files and how they can be useful while migrating a package from one environment to another. Ex: From Development to Staging/QA. In this post we will be making use of one of the old packages, “Importing data from a text file using a package” which we created in one of the previous posts.

Configuration Files:
Configuration Files contains the connection strings for various sources and destinations, package variables and also the expressions. We can modify these properties without even opening up the package. At the run time, the SSIS engine looks for the Configuration File. If the file does not exists then it takes the configuration/variable information contained in the SSIS package. If the file exists, then the information in the Configuration File is made use of. If any variable/configuration information is missing in the Configuration File, that particular value of the configuration parameter/variable from the SSIS package is made use of.

Creation of Package Configurations:

The following image shows the SSIS Package for importing the text file from the location C:\SSIS\TextFiles:

Let us assume that we are migrating this package to a staging server and connecting to a different database (northwind) and placing the incoming text file in the location C:\ImportTextFiles. In our case the development server and the staging server is the same that is the local machine. In this post we are going to discuss the modification of the package properties at run time without actually opening up the package and modifying it so that this package executes successfully. In such scenarios, the concept of Configuration Files comes into the picture.

Right click on the control flow pane and click on the Configurations menu. This will open up the configuration wizard. The following image shows us how to open up the configuration editor in SSIS:

Opening up the Package Configurations:

Clicking on the Package Configurations opens up the Configuration Organizer. The following figure shows the Package Configuration Organizer.

Clicking on the Add button of the Organizer opens up the Package Configuration Wizard. The figure below shows the Package Configuration Wizard.

Clicking on Next takes us to the page where the wizard asks us to enter the type of Configuration as shown in the figure:

SSIS supports various types of Configuration Types:
1. XML Configuration File:
In this type of Package Configuration, the configuration parameters are stored in a xml file in the file system (hard disk). This is the easiest way of creating the package configurations. The properties of the package can be altered at run time by modifying this xml file. This article makes use of this configuration type.

2. Environment Variables:
The package configurations can be provided using the Environment Variables. This is beyond the scope of this article. 

More information can be read on MSDN at http://msdn.microsoft.com/en-us/library/ms141682.aspx.

You can refer to the following site for an example: http://www.mssqltips.com/tip.asp?tip=1405

3. Registry Entry:
The configuration values can also provided using a Registry values. This is also beyond the scope of this article. More information can be read at:
http://msdn.microsoft.com/en-us/library/ms141682.aspx .

4. Parent Package Variable:
In this type, the configuration values are stored in a variable of the parent package. This variable is used to update the properties of the child package at the run time. This is also beyond the scope of this article.

5. Sql Server:
In this configuration type, the configuration values are stored inside the database in a table. This is very similar to the xml configuration type but the values are stored inside the database. We can make use of DML statements to modify the configuration values. The advantage of this type is that since the database is backed up regularly, the configuration values are also backed up. But if the configuration is stored in the xml file then we have to back it up manually. Another advantage is that DML is more familiar to the database developers and DBAs since not many people know xml.
For the purpose of our demo, let us select the XML Configuration type. In the text box below, the wizard asks us to enter the location and name of the XML configuration file. Click on the browse button and specify the path and name for the xml file and click next. The next screen shot shows the various configuration properties that can be changed at the run time of the package. It includes the connection managers, variables and expressions.

Normally, we prefer to include all the connection manager connection strings and the variable values used in the package. Since we are moving to a different server, the select the connection string for both the connection managers and the variable value. The figure below shows the partial screen shot of the property selection.

Choose the necessary configuration properties and click next. In the next screen give the configuration name and click finish to exit the wizard. Then click close on the Package Configuration Organizer. The following xml is the xml file which is generated from the Package Configuration Wizard.

<?xml version=”1.0″?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy=”REDMOND\v-vipul” GeneratedFromPackageName=”TextFileImport”
GeneratedFromPackageID=”{297BD82A-0E0B-444B-A6B0-D606CD5171A1}”
GeneratedDate=”8/13/2008 9:37:56 PM”/>
</DTSConfigurationHeading>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[Applications].Properties[ConnectionString]“ValueType=”String”>
<ConfiguredValue>C:\SSIS\TextFiles\Applications.txt</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[DecipherDestination].Properties[ConnectionString]” ValueType=”String”>
<ConfiguredValue>
Data Source=.;Initial Catalog=AdventureWorks;
Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;
</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Variables[User::TableName].Properties[Value]” ValueType=”String”>
<ConfiguredValue>Applications</ConfiguredValue>
</Configuration>
</DTSConfiguration>

We can observe that the configuration wizard automatically populated this file with the values of the connection managers and the variables.

Now let us assume that this package is migrated to staging and the database to which the data is imported is changed to Northwind rather than AdventureWorks. Further let us assume that the incoming files are placed in the directory C:\ImportTextFiles rather than the directory C:\SSIS\TextFiles as in the original package.  In SSIS, we can modify the configuration file which holds those configuration values. Copy the text file and place it in the location C:\ImportTextFiles. Open up the configuration file and modify the connection manager’s connection string as shown below.

<?xml version=”1.0″?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy=”REDMOND\v-vipul” GeneratedFromPackageName=”TextFileImport”
GeneratedFromPackageID=”{297BD82A-0E0B-444B-A6B0-D606CD5171A1}”
GeneratedDate=”8/13/2008 9:37:56 PM”/>
</DTSConfigurationHeading>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[Applications].Properties[ConnectionString]“ValueType=”String”>
<ConfiguredValue> C:\ImportTextFiles\Applications.txt</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[DecipherDestination].Properties[ConnectionString]” ValueType=”String”>
<ConfiguredValue>
Data Source=.;Initial Catalog=NorthWind;
Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;
</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Variables[User::TableName].Properties[Value]” ValueType=”String”>
<ConfiguredValue>Applications</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Save the configuration file and execute the SSIS package. We will see that the table named Applications is now created in the northwind database rather than in AdventureWorks database. In this way, the Package Configurations provides us with a neat interface with which we can alter the properties of the package at run time without manually opening up the package and modifying it.