Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How To Support Multiple SQL Server Package Configurations in SSIS


By:   |   Last Updated: 2012-08-08   |   Comments (4)   |   Related Tips: More > 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:

Click Add and specify SQL Server as the configuration type

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:

ALTER TABLE [dbo].[SSIS Configurations] ADD [ApplicationName] [nvarchar](128)

  1. 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:

bcp "select *, 'A' from dbo.[App_A_Configurations]" queryout APP_A.txt -d config -c -T -S localhost

  1. 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):

bcp "dbo.[SSIS Configurations]" in APP_A.txt -d config -c -T -S "localhost\SQL2012"

  1. 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
  1. 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:

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.


Last Updated: 2012-08-08


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 30, 2012 - 11:10:10 AM - Ray Barley Back To Top

ConfigurationFilter sounds like a good idea except that I want a single package to be able to specify at runtime what configuration to use.  The only way to do that is have the package use a single ConfigurationFilter value and specify something else that is then used to determine which rows in the configuration table should be used.  ApplicationName works for my scenario.

The best example I have is I need a package to specify the right configuration when it's in development, test, and production.  I don't want to have to change the ConfigurationFilter each time I move between environments.


Tuesday, October 30, 2012 - 8:26:58 AM - Patrick Back To Top

An interesting approach, but why don't you use the ConfigurationFilter column for filtering by Application?


Wednesday, August 22, 2012 - 1:08:29 PM - Greg Robidoux Back To Top

The attachment has been fixed.

Thanks


Wednesday, August 22, 2012 - 12:20:05 PM - Gregsoc Back To Top

The attachment to this post is not working.

Can it be fixed please?


Learn more about SQL Server tools