SSIS: Package Configuration

The package can be transferred across various environments like development and unit testing, system testing, UAT and production. Most packages will have environment specific variables like connection string to a database or path to a flat file, or user defined variables etc. that would be impacted while moving the package across environments as part of deployment process. Hence, it is mandatory to change these environment dependent variables when the package is transferred across environments. Package configurations help in managing such changes without actually opening and editing the SSIS package in Business Intelligence Development Studio (BIDS). 

After deploying the package to a different machine (using SQL Server or file system deployment mode) it is mandatory to copy the related package configuration files on to that machine. If the package is scheduled to run through a SQL Agent job, the configuration file should be added while creating the job so that package will read the information from the configuration file. While executing the job, SQL Agent will take the design time values for connection strings if the package configuration file is not supplied. 

From the Package properties, go to the Misc section.

There is a property called Configurations, click on the ellipsis then we get the Package Configurations Organizer window.

 

Select check box of Enable package configurations

There are 5 configuration types available with package configurations.
•XML configuration file
•Environment variable
•Registry entry
•Parent package variable
•SQL Server

1. XML configuration file

Select the Configuration type as 'XML Configuration file' as follows:

 

2. Parent Package Variable:
How to pass a Parent variable value to Child Package?

We can pass the parent variable to child package by using Package configuration:  Parent Package Variable.

2.1 Create parent variable

  FilePath - Parent - String - C:\RK\file.txt

2.2 Drag an 'Execute Package Task' in Control Flow and configure it to start  child package.

2.3 Go to Child Package and create a variable with same name as your parent package variable.

2.4 Add Package configurations

 

2.5 "Enable Package Configuration", choose Configuration type as "Parent Package Variable" and type the name of the variable.

 

2.6 Click 'Next' button and select the 'Value' property of the child package variable. 

 

2.7 click 'Next' and 'OK' buttons

2.8 To test the package, I added sample Script Task with a messageBox to show the value of the parent package.

 3. Environment Variable:

The values of configurable properties are stored in the variables. In order to select the configuration type as Environment Variable, follow the steps:

1. Navigate to My Computer - Properties - Advanced tab.

2. Go to Environmental variables and create the variable (User variable or System variable).

Ensure that same variable is available on the target server where package is to be deployed.

3. Value of the variable on the target server may or may not be same as the value on development machine.
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Select the Environment Variable from Dropdown. EnvTest is the environment variable name.

 

 

 

 

 Click Next and select the target property for this variable .

 

 

 

Now, the package will read the selected property from this variable.

If the database connection manager’s connection string is selected as Target property, then the value of environment variable needs to be changed if there is some change in the database connection string.

 

 4. SQL Server:

This option is used when the configuration variables are to be stored in a SQL Server table.

Select configuration type as SQL Server and select the Connection Manager of your choice as shown.

If the table is not available to store these values, the wizard allows creating a new table.

 

 5. Registry Entry:

Windows registry is another mode to store package configuration values. The package configuration value can be stored either in an existing registry key or in a new registry key created. It can further be changed to different value of the key if required at a later point of time.

 

Create a new key in HKEY_CURRENT_USER section of registry. Go to registry editor and create a new key.