Share via

Using a View to Mask Multi-Server SSIS Package Configurations

  • Article

Introduction

SSIS Package Configurations allows property values which are particular to a given SQL Server instance to be customized for that instance without modification to the underlying package. To quote SQL BOL, "SQL Server Integration Services provides package configurations that you can use to update the values of [package] properties at run time." If unfamiliar with package configurations, the following article is a great place to get familiar with the concepts: http://technet.microsoft.com/en-us/library/ms141682.aspx.

Problem Definition

One supported source for setting the properties of objects in SSIS is the SQL Server package configuration table. When using a SQL Server configuration table, the configuration information for a particular package is differentiated from that of other package configurations in the same table by a package filter. But, the package filter does not differentiate between multiple instances of the same package as might be required when running the same package against different targets, particularly if those targets are named instances or otherwise require instance-specific parameter values. So, in this article, that problem is addressed by modifying the default table schema of the package configuration table and using an instance-specific view in its place.

Alternatives to the SQL Server package configuration table include XML configuration files, Environment variables, and Registry entries. But, all of these solutions require management of package configuration information that is distributed throughout the enterprise, whereas this article describes a completely centralized approach.

Setup

When choosing the SQL Server configuration type during package development, the Package Configuration Wizard automatically suggests an appropriate configuration table in which to store the package configuration. The default table schema consists of the following four columns: ConfigurationFilter, ConfiguredValue, PackagePath, and ConfiguredValueType. But, nothing prevents additional columns from being added to the configuration table.

For purposes of this article, it is best to cancel the Package Configuration Wizard and setup the configuration table in advance. First, select or create a database to host the configuration table and view. Then, create the configuration objects. Note that, in this example, columns ConfigItemID, Host, and Login are added to the table schema in addition to the default columns. A view is then created to filter the configuration table by host and login.

CREATE TABLE  [dbo].[SSISConfig](
    [ConfigItemID] [int] IDENTITY(1,1) NOT NULL,
    [Host] [nvarchar](128) NULL,
    [Login] [nvarchar](128) NULL,
    [ConfigurationFilter] [nvarchar](255) NOT NULL,
    [ConfiguredValue] [nvarchar](255) NULL,
    [PackagePath] [nvarchar](255) NOT NULL,
    [ConfiguredValueType] [nvarchar](20) NOT NULL
) ON  [PRIMARY]
GO
 
CREATE VIEW  [dbo].[SSISRuntime]
AS
    SELECT ConfigurationFilter
        ,ConfiguredValue
        ,PackagePath
        ,ConfiguredValueType
    FROM dbo.SSISConfig c
    WHERE [Host] = HOST_NAME()
    AND [Login] = SUSER_NAME()
GO

Next, complete the Package Configuration Wizard and specify the view as the configuration source:

  1. In SSDT, under package properties, click Configurations.
  2. In the Package Configurations Organizer, check Enable package configurations, and click Add.
  3. Under Configuration type, select SQL Server.
  4. Under Connection, click New to create a new Connection manager if a connection manager of type "Native OLE DB\SQL Server Native Client 11.0" doesn't already exist. Select the database that hosts the configuration table and view.
  5. Under Configuration table, select the view (not the table) created above.
  6. Under Configuration filter, choose any name that will uniquely distinguish this package from other packages. Avoid any server-specific references.
  7. Click Next and complete the wizard.

The host and login columns will be left NULL by the Package Configuration Wizard since it is unaware of these columns.

Nevertheless, the default package configuration generated by the wizard can be used as a template to generate instance-specific versions of the configuration by adding the appropriate host and login values as well as instance-specific parameter values. For example:

INSERT SSISConfig (Host, [Login], ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType)
SELECT
     'ServerA' [Host]
    ,'DOMAINA\ServerA_SQLAgent' [Login]
    ,ConfigurationFilter
    ,CASE
        WHEN PackagePath LIKE '%ServerName%'  THEN 'ServerA\NamedInstance'
        WHEN PackagePath LIKE '%page_count_threshold%'  THEN CONVERT(nvarchar(255), 2560)
        ELSE ConfiguredValue
     END [ConfiguredValue]
    ,PackagePath
    ,ConfiguredValueType
FROM SSISConfig
WHERE ConfigurationFilter = 'MSXMaintainIndexes'
AND Host IS NULL

How It Works

Depending upon which login makes the request from which host, the corresponding package configuration for that login and host is returned. Login, in this context, refers to the SQL Agent account on the host where the package will execute. For this example, a unique combination of host and login is required. But, technically speaking, the view can be created with any filter appropriate for the environment being administered. The package configuration in this illustration is filtered by the requesting host and login, but could just as easily be filtered by time-of-day, if the table schema and view were configured accordingly.

Application

The approach described in this article may be used in conjunction with Multi-Server Administration (MSX) to deploy the same SSIS-based job to multiple targets. The job is configured to read the SSIS package from the MSX Master and the SSIS packages configured to read configuration information from a table likewise hosted in a database on the MSX Master. Thus, everything is tidily kept in a single location and administration is completely centralized with all updates being made solely at the MSX Master.

Caveats

When editing an existing package with the Package Configuration Wizard, unless a package configuration exists with a corresponding Host/Login filter for you (login) connecting from your dev workstation (host), it will not be visible to the Package Configuration Wizard and will not be found. If you proceed, you'll need to reselect any properties or variables as though doing so for the first time. Again, since the wizard isn't instance-aware, use caution to avoid creating a duplicate set of values, or at least be aware that manual cleanup of the table may be required after making any such changes.

See Also