How To Support Multiple SQL Server Package Configurations in SSIS

By: Ray Barley   |   Updated: 2012-08-08   |   Comments (4)   |   Related: > Integration Services Configuration Options

Problem

I am using the SQL Server package configuration option with my SSIS 2005 and 2008 packages.  One issue that I am trying to resolve is how to specify multiple configurations within a single SSIS Configuration table.  For instance I have several applications that use SSIS packages and I want to be able to store all the configurations together in a single table when I deploy.  When a package executes I need a way of specifying the "application" and having SSIS automatically handle the package configuration based on the application.  Can you provide an example of how to do this?

Solution

By default when you setup SQL Server package configuration in an SSIS package, you run the Package Configurations Organizer and create a table with the following schema to store the configuration data:

USE [config]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
 [ConfigurationFilter] [nvarchar](255) NOT NULL,
 [ConfiguredValue] [nvarchar](255) NULL,
 [PackagePath] [nvarchar](255) NOT NULL,
 [ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]
GO

The definition of each column is:

• ConfigurationFilter is used by the SSIS package to retrieve the row
• PackagePath identifies a property of an object in the SSIS package that gets set via the configuration entry
• ConfiguredValue is the value to assign to the object property
• ConfiguredValueType is the type of the ConfiguredValue; e.g. Int32, Int64, String, DateTime, etc.

Based on the above default schema, there is not an easy way to store multiple configurations in the table; i.e. how do we store a connection string configuration for our DEV, TEST and PROD environments and have our SSIS packages grab the right one based on our runtime environment?  

In this tip I will walk through the following steps showing how to easily implement a solution:

• Package configuration setup
• Package configuration deployment
• Testing the solution

SQL Server Integration Services Package Configuration Setup

To create a package configuration, right click on the Control Flow design surface and select Package Configurations from the popup menu.  Click Add and specify SQL Server as the configuration type as shown below:

Note the configuration table; click New to create a new table for each "application".  Decide on a naming convention for the package configuration table; e.g. App_X_Configurations.  After creating the new configuration table, make sure to select it from the dropdown list in each configuration in each SSIS package in the application.  You will now have multiple package configuration tables in your development environment.

SQL Server Integration Services Package Configuration Deployment

When you are ready to deploy your SSIS packages to your target environment, follow these steps:

1. Create a package configuration table in your target database (see the script at the beginning of this tip) and add an ApplicationName column to the table:

2. Use BCP to extract each of your application-specific package configuration tables to a text file; note that I added a column that will be used to populate the ApplicationName column:

3. Use BCP to import your application-specific package configuration text files into the single package configuration table in your target environment (I have a named SQL2012 instance that I'm using as my target):

4. Create a view in your target environment for each application-specific package configuration table. Name the view the same name as your application-specific package configuration table. Add a WHERE clause to filter on your application name:

CREATE VIEW dbo.App_A_Configurations
AS
SELECT 
  ConfigurationFilter
, ConfiguredValue 
, PackagePath
, ConfiguredValueType
FROM
  [dbo].[SSIS Configurations]
WHERE
  ApplicationName = 'A'
GO

5. Consider using a SQL Server alias to specify the SQL Server instance that holds your configuration database; see the tip Using a SQL Server Alias for SSIS Package Configuration Database Connection String

Testing the SQL Server Integration Services Package Configuration Solution

To test the solution I did the following:

• Created two SSIS packages each with their own package configuration tables; App_A_Configurations and App_B_Configurations
• Added a package configuration to each package to set the value of the package variable START_DATE
• BCP'd the App_A_Configurations and App_B_Configurations tables out to a text file using the queries shown above where I added a column with the value 'A' for the App_A_Configurations table and 'B' for the App_B_Configurations table
• Added the ApplicationName column to the [SSIS Configurations] table in another SQL Server instance; i.e. my target instance
• BCP'd each text file into the [SSIS Configurations] table in the target instance
• Tested each package by setting a breakpoint on a script task and examining the value of the START_DATE variable; verified that the value matched what was in the package configuration table based on the application name

After performing the BCP steps above, the following shows the contents of the [SSIS Configurations] table in the target instance:

As you can see there are two rows where the ConfigurationFilter is START_DATE and each row has a unique application name and a different value.

Next Steps

• Take a look at the earlier tip SQL Server SSIS Package Configuration to review the details of setting up SQL Server package configuration.
• You can download a zip of the sample code here.  Take a look at the README.txt file for the details.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

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

View all my tips