SSIS External Configuration File to Dynamically Update Package

Problem

Often, SSIS packages require that some of their properties change at runtime and require dynamic values to access the correct SQL Server data. It is not possible to edit the package each time using Visual Studio as we do during ETL development, so how can this be accomplished at run time with a config file?

Solution

This article illustrates how to use a Microsoft SQL Server Integration Services (SSIS) external configuration file to enable the properties of package objects to be dynamically updated at run time.

Adding a SSIS Package Configuration

To add package configuration, ensure you are not selecting any task in the control flow, then press F4 to show the properties tab. In the package properties, click on the button on the right side of the “Configurations” property.

After clicking on the button, the Package configurations organizer dialog appears. As shown below, this dialog contains a checkbox that enables the package configurations, a grid that contains the defined package configurations, and the “Add,” “Remove,” and “Edit” buttons used to manage the existing configurations. The existing configurations are listed in the order in which they will be loaded when the package runs.

Each configuration option has the following properties:

• Configuration Name: The name of the configuration
• Configuration Type: There are five types of configurations:
  • XML configuration file: the configuration is stored within an external XML file
  • Environment variable: the configuration value is stored within an operating system environment variable
  • Registry entry: the configuration value is stored within a registry entry.
  • Parent package variable: the configuration value is taken from a parent SSIS package.
  • SQL Server: The configuration is stored within a SQL Server table.
• Connection String: The connection string used to access the configuration. For instance, when using an XML configuration file, the connection string is a file path.
• Target Object and Target Property: These properties are empty if the configuration type is an XML configuration file or a SQL Server table since those types contain several configuration mapping. For other configuration types, these values contain the target SSIS property and value mapped to the configuration.

Now, let’s add a new configuration and select the configuration type as “XML configuration file.”

Then, there are two options to specify the XML configuration file path:

• Directly, by selecting a file path. (if the file does not exist, it will be created).
• Using an operating system environment variable.

This tutorial will pass the file path directly, as shown above.

Next, we should specify the SSIS package object properties that we need to retrieve their values from the configuration file. As shown in the screenshot below, we selected three properties related to the data flow task: AutoAdjustBufferSize, DefaultBufferMaxRows, and DefaultBufferSize.

The values stored initially within the XML configuration file are the ones that currently exist in the SSIS package.

Finally, we can specify a name for the configuration. A summary of the created configuration is provided, as shown in the below screenshot.

Once we click on finish, we can note that a configuration is added to the grid in the package configuration organizer dialog.

XML Configuration File Content

After adding the XML configuration file, a new file with a .dtsconfig extension is created in the specified path in the file system.

If we open the .dtsconfig file with a text editor, we will find that it is an XML file that contains the properties’ values selected in the wizard.

<?xml version="1.0"?>
<DTSConfiguration>
   <DTSConfigurationHeading>
      <DTSConfigurationFileInfo GeneratedBy="Admin" GeneratedFromPackageName="Package" GeneratedFromPackageID="{0D188FE3-49E8-43BD-B6D8-5E81A39D5227}" GeneratedDate="12/10/2022 9:18:39 PM"/>
   </DTSConfigurationHeading>
   <Configuration ConfiguredType="Property" Path="\Package\Data Flow Task.Properties[AutoAdjustBufferSize]" ValueType="Boolean">
      <ConfiguredValue>0</ConfiguredValue>
   </Configuration>
   <Configuration ConfiguredType="Property" Path="\Package\Data Flow Task.Properties[DefaultBufferMaxRows]" ValueType="Int32">
      <ConfiguredValue>10000</ConfiguredValue>
   </Configuration>
   <Configuration ConfiguredType="Property" Path="\Package\Data Flow Task.Properties[DefaultBufferSize]" ValueType="Int32">
      <ConfiguredValue>10485760</ConfiguredValue>
   </Configuration>
</DTSConfiguration>

From the file content, we can see that the configuration file is composed of two main sections:

1. The configuration header (defined within the <DTSConfigurationHeading></DTSConfigurationHeading> XML tags) contains the file metadata, such as the package ID, the creator, and the creation date.
2. The included configurations. Each configuration is defined within two <Configuration></Configuration> XML tags. As shown in the code above, each design has an inner <ConfiguredValue> XML tag that contains the configured value besides the following attributes:
   1. ConfiguredType: the configured object type
   2. Path: The path of the configured object within the SSIS package.
   3. ValueType: the data type of the configured object

Experiments

The test how the external configuration file works, after creating the XML configuration file with the AutoAdjustBufferSize property value set to False, DefaultBufferSize set to 10485760, and DefaultBufferMaxRows set to 10000, I changed these values to True, 10000000, and 20000, respectively.

Now, I executed the SSIS package and rechecked these property values. As shown below, the values are updated from the configuration file.

Using Environment Variable to Store the Configuration File Path

As stated before, we can pass the configuration file path using an operating system environment variable.

To create an environment variable using Windows, search for “Advanced system settings” in the search bar beside the start button and click on the “View advanced system settings” option.

When the system properties dialog appears, click on the “Environment variables” button as shown below,

In the environment variables, you can add user environment variables only available for the current user and system environment variables available for all users.

In this tutorial, we added a system environment variable named “SSISConfig” with a value equal to the configuration file path we already created.

Now, let’s get back to the package configuration organizer dialog and edit the configuration we previously created. In the “Select configuration type” section, we select the “Configuration location is stored in an environment variable” option and select the “SSISConfig” variable from the drop-down list.

Note: You may need to restart Visual Studio to refresh the environment variables list.

When using an environment variable to read the file path, the configuration file should already be created. And you cannot add more configurations from the wizard. Once we select an environment variable and click “Next,” the summary form appears directly.

We note that the configuration type changed to “Indirect XML configuration file,” and the connection string changed to the environment variable name.

Is the Configuration File Required to Run the Package?

Well, the answer is NO. The package will typically run with the currently configured values if the XML configuration file is renamed, removed, or does not exist.

Next Steps

Before putting your SSIS package into production, it is highly recommended to read more about SSIS performance optimization techniques and the bad habits that you should avoid:

• SSIS Bad Habits: Decreasing SSIS Package Performance
• SSIS Bad Habits: Inefficient Data Staging Strategies
• SSIS Bad Habits: Wrong OLE DB components configuration
• SSIS Bad Habits: Inefficient data staging strategies – Part 2
• SSIS Bad Habits: Slow lookup transformations
• SSIS Bad Habits: Using Sort and Merge Join Components