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:
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.
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.
[Host] = HOST_NAME()
[Login] = SUSER_NAME()
Next, complete the Package Configuration Wizard and specify the view as the configuration source:
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
SSISConfig (Host, [Login], ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType)
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.
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.
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.