Getting started with SSIS External Configuration File

By:   |   Updated: 2023-02-06   |   Comments (2)   |   Related: > Integration Services Configuration Options


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?


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.

Package properties configurations

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.

Package configuration organizer

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."

Package configuration wizard select configuration type

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.

Package configuration wizard select properties to export

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.

Package configuration wizard completing the wizard

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

Package configurations organizer new grid

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.

New file created

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"?>
      <DTSConfigurationFileInfo GeneratedBy="Admin" GeneratedFromPackageName="Package" GeneratedFromPackageID="{0D188FE3-49E8-43BD-B6D8-5E81A39D5227}" GeneratedDate="12/10/2022 9:18:39 PM"/>
   <Configuration ConfiguredType="Property" Path="\Package\Data Flow Task.Properties[AutoAdjustBufferSize]" ValueType="Boolean">
   <Configuration ConfiguredType="Property" Path="\Package\Data Flow Task.Properties[DefaultBufferMaxRows]" ValueType="Int32">
   <Configuration ConfiguredType="Property" Path="\Package\Data Flow Task.Properties[DefaultBufferSize]" ValueType="Int32">

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


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.

Test how the external configuration file works

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

updated values after SSIS package executed

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.

Advanced system settings

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

System variables Advanced

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

System variables Advanced Environmental variables

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.

Package configuration wizard Select configuration type Environmental variable SSISConfig

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.

Package configuration wizard Completing the Wizard configuration name change

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

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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Hadi Fadlallah Hadi Fadlallah is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at He holds a Ph.D. in data science focusing on context-aware big data quality and two master's degrees in computer science and business computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-02-06

Comments For This Article

Wednesday, March 1, 2023 - 4:20:15 PM - Hadi Fadlallah Back To Top (90966)
Patj, you're right. Still, several companies still use this approach especially when their legacy applications were developed to work with the MSDB database.

Friday, February 24, 2023 - 2:01:05 AM - Patj Back To Top (90949)
Do people still use these? I thought most had moved on to the Data Catalog Environments and SSIS environment variables now. Much easier.

get free sql tips
agree to terms