This article describes a method for centrally managing configuration for SSIS packages while using different configurations for different environments. If you haven’t juggled multiple configuration files for multiple packages and wondered if there was a better way to organize them, you might not appreciate what problem I’m trying to solve. Unless local coding standards are defined, understood, and followed rigidly by all developers, it can be a nightmare to figure out what config file(s) a package was using last night on server XYZ when it reported an error, and what config file(s) (or worse, manual changes) do I have to make to test it.

I recently joined a company that had quite a few SSIS packages, none using configurations. Most were converted straight from DTS, all used EncryptSensitiveWithUserKey. (No comment about how they got to this state.) I wanted the simplest configuration scheme I could think of to introduce to the developers.

I wanted to make it as easy to work with as possible, while also making it easy for me to deploy and maintain. The most important attribute I wanted would be to have it automatically choose the correct configuration set, so any package opened by a developer for dev or test would not connect to production data.

There are ways to approach this issue with configuration files, but there are issues with keeping track of all the files through source control and deployment. I wanted to use SQL Server configuration because a single table can hold many configuration sets. Instead of tracking config file changes with source control, config table changes are logged to a history table by a trigger. Assuming all deployment targets are in the same domain, one table can be used to configure packages running anywhere. This is better than having many config files scattered around the network, but at first glance it seems that the packages can only have one configuration.

Configuration for multiple environments (say development, test, and production) could be implemented by using indirect configuration of the configuration connection manager to pick different databases to find separate configuration tables. This would require a few config tables and an environment variable (or registry key) setting on every server and developer computer. I still thought this was more complicated than it needed to be.

Using several tables and one environment variable per computer is better than a hoard of XML files, but I still wandered if there was a way to cram everything in one table. It turns out there is. Anyone who has tried SQL Server configuration knows that it creates a table with four fields. I had already accessorized it with indexes, a trigger to log changes to a history table, an identity column to aid the trigger, ModifiedOn and ModifiedBy fields, and so on. SSIS didn't care about the extra fields as long as the four fields it needed were there. My bright idea was to add a sequence field to allow multiple values for each property, and create a view that would return different subsets of the base table depending on where the package is executed. SSIS was happy to use the view, and could even create & update values from BIDS.

-- Standard SQL Configuration table
CREATE TABLE dbo.SSIS_Config (
    ConfigurationFilter VARCHAR(100)NOT NULL,
    PackagePath NVARCHAR(255)NOT NULL,
    ConfiguredValueType NVARCHAR(20)NOT NULL,
    ConfiguredValue NVARCHAR(1000)NULL)
-- Multi-valued SQL Configuration table
-- Refer to the accompanying code file for the complete version
CREATE TABLE dbo.SSIS_Config_base (
    ConfigurationFilter VARCHAR(100)NOT NULL,
    PackagePath NVARCHAR(255)NOT NULL,
    ConfiguredValueType NVARCHAR(20)NOT NULL,
    ConfiguredValue NVARCHAR(1000)NULL,
    EnvironmentEnum TINYINT NOTNULL DEFAULT 0)
-- View used in package configuration to select properties by environment
CREATE VIEW dbo.SSIS_Config AS
SELECT      ConfigurationFilter,
            PackagePath,
            ConfiguredValueType,
            ConfiguredValue
FROM dbo.SSIS_Config_base b
JOIN(
      -- Choose environment based on where package is running
      SELECT CurrentEnvironment = CASEWHEN HOST_NAME()= '<production server>' THEN 1
                                      WHEN HOST_NAME()= '<test server>' THEN 3
                                      ELSE 2 END) e
ON b.EnvironmentEnum = 0 OR b.EnvironmentEnum = e.CurrentEnvironment

The subquery in the view is the key concept here. I show a choice of three environments based on the HOST_NAME() function, but you are only limited by your imagination. More environments can be defined, and there are many different ways to define them. The point is that if you can write a subquery that correctly chooses the environment for package configuration on your systems, that works wherever the package might run and whoever runs it, this view can select the correct configuration set from one central table. Other possible inputs are USER_NAME() and PROGRAM_NAME() (this can be set in a package using the Application Name property of the SQL config connection manager). If your rules can’t be boiled down to one expression, you could have another table that defines the environment for every combination of user, host, and program name.

To elaborate, I added a tinyint field to the standard configuration table named EnvironmentEnum that has values in the range 0-3. Properties that are the same in all environments have one record with a value of 0, properties with alternate values have three records enumerated 1, 2, and 3 to provide separate values for each environment. It works seamlessly with package development. When new configuration sets are created they default to a single all-environment record (enum=0). Single-valued properties can be modified and saved in BIDS as usual, and if a multi-valued property is modified and saved only the development version is affected. There are stored procedures in the code file to split single-valued properties into multi-valued, merge them back to single-valued, and to set all alternate values for a given property.

To see all alternative settings for each property requires a PIVOT view.

/*    SSIS Configuration multi-environment maintenance view
       View one config item per line with all variations by pivoting on environment
*/CREATE VIEW dbo.SSIS_Config_AllEnv AS
SELECT      ConfigurationFilter,
            PackagePath,
            SUBSTRING(t.ConfiguredValueTypeList, 4, 999) AS ConfiguredValueType,
            [0] AS GlobalValue,
            [1] AS ProductionValue,
            [2] AS DevelopmentValue,
            [3] AS TestValue
FROM(
      SELECT      ConfigurationFilter,
                  PackagePath,
                  EnvironmentEnum,
                  ConfiguredValue
      FROM  SSIS_Config_base
)base
PIVOT(MAX(ConfiguredValue) FOR EnvironmentEnum IN ([0], [1], [2], [3])) pvt
-- Use CROSS APPLY ... FOR XML PATH trick to concatenate different ConfiguredValueType entries
-- There shouldn't be any differences, but if there are it should be made obvious
-- The initial space-semicolon-space delimiter is removed by SUBSTRING above
CROSS APPLY (
      SELECT DISTINCT' ; ' + ConfiguredValueType
      FROM dbo.SSIS_Config_base
      WHERE ConfigurationFilter = pvt.ConfigurationFilter
            AND PackagePath = pvt.PackagePath
      FOR XML PATH('') ) t ( ConfiguredValueTypeList )

I created a simple demonstration package that uses a script task to produce a message box built from package variables. The variable value properties were assigned to a new configuration set. Note that the Configuration String refers to the above view, not the table.

With the original design values, running the package produces this dialog:

Now I go to Management Studio and expand the configured properties to have different values. This screen shot shows how the items were created originally by BIDS, and what they look like afterwards.

This screen shot shows the underlying records in the base table. When the package is loaded it will pick one consistent subset of these records through the SSIS_Config view. A production package will probably have many configuration sets, and the correct subset of each of them will be used.

I saved the package to msdb on my production server, although it could have been saved to a file share or any other location visible from all the target environments. Then I ran the package from my laptop, the test server, and the production server to show the three different configurations. Note that I only had to deploy one copy of the package, and no additional files or other artifacts.

It’s more impressive when you’re reconfiguring connections and controlling the package on a grander scale, but hopefully this demo shows how the technique works. My packages, when not in production, automatically connect to different servers, use local test FTP sites, don’t send email outside our department, and log to a different location.

I think this is the easiest solution for my needs. It is easy for developers who are new to SSIS to pick up, especially since I created a template package that has configuration, logging, error handling, and email all set up. I don’t have to worry about developers accidentally wiping out production data or sending erroneous emails to customers. If a package reports an error I can easily check what configuration settings it is using, and if necessary who was the last person to change them.

The attached code file has the complete code for all tables, views, and procedures. An unrelated issue with SQL Server configuration tables that I found while researching this project is that the ConfiguredValueType field is case-sensitive when used by SSIS, and improperly-cased values cause the configuration item to be ignored with no warning. The table definition in the code file shows how to use a case-sensitive collation and check constraint to only allow proper-cased values for ConfiguredValueType.