In Part 1 of this series, we learned about the benefits of using a SQL Server Integration Services (SSIS) management framework and what areas need to be standardized to realize these benefits. In this article, we will learn how to standardize one of those areas: configuration management.

Let us start with reviewing the goals of a good configuration management solution:

  1. Eliminate manual package modifications. Implementing configuration management eliminates the need to open and modify SSIS packages when moving them from development to testing and from testing to production. The configuration values (also known as runtime values) are stored and managed outside the packages and used by the packages at runtime. This design reduces the possibility of errors that can occur by a developer or a support person editing a package and overlooking some of the changes required for the package to run successfully.
  2. Simplify modifications due to changes in the environment. Environment variables include file paths for source or destination files as well as server and database names. These variables change as we move our packages from development to testing and from testing to production. They can also change in a package (or set of packages) that have been in production for a while. When these variables change, the framework helps ensure that it is easy to identify and make the appropriate modifications so that packages affected by these changes continue to function normally.
  3. Simplify the management of variables. Each SSIS package has some unique variables that may need to be modified at some point to change the behavior of that particular package. Anytime you encounter a hardcoded value in your package, there is the possibility that it will change in the future. Managing such variables outside the package fulfills our first goal of eliminating manual package modifications after the package has been developed.
  4. Eliminate redundant storage of configurations. Certain configurations such as database connection strings are used in multiple packages to connect to the same set of sources and destinations. Having a single store for common configurations that are shared by multiple packages simplifies the overall management of changes.
  5. Eliminate errors due to environmental changes. Implementing configuration management reduces and sometimes eliminates errors that can be caused by non-standard package implementations. In addition, it reduces the time it takes to implement system or environment modifications and eliminates the need to find and edit every single package that might be affected.

The Configuration Strategy

To meet the goals of a good configuration management solution, we can implement a three-pass configuration strategy that is designed to make the SSIS packages machine independent. At the heart of this configuration strategy is a centralized configuration database with multiple tables that contain all the configuration information required by one or more packages. The goal is for all packages to read relevant configuration information from this centralized database, regardless of what server the package is running on. The connection information for this centralized database is held in an XML configuration file, which is referenced from the SSIS packages and consumed prior to receiving other configuration information. The package reads the configuration information in the following order:

Pass 1. The package reads the XML configuration file, which contains a single connection string to the configuration database. With this design, only a single change to the XML configuration file is needed if the connection string changes.

Pass 2. The package reads a configuration table that contains all common configurations. This configuration points to the database indicated in the XML configuration file.

Pass 3. The package reads a configuration table that contains package-specific configurations.

Implementing the Configuration Management Framework

Implementing the configuration management framework consists of setting up the following components:
A centralized configuration database. The main benefit of using the configuration database is that it enables the implementation of database and role-based security. Typically, a configuration database contains one common configurations table and one package-specific configurations table. Variations are possible. For example, a configuration database might have only a common configurations table or have a common configuration table and two package-specific configurations table.
The common configurations table contains the configurations that are common to multiple packages. They include connection strings and shared folder paths.
The package-specific configurations table contains configurations that are specific to a single package. They include package variables, connection strings that are not shared by any other package, connection strings that have security credentials unique to a particular package, and package-specific object properties. The package-specific configurations are stored in a separate table for manageability and security reasons.
In the sample implementation that we will be discussing, the centralized SSIS configuration database will be named SSISManager. It will contain one common configurations table named SSISCommonConfigurations.
XML configuration (.dtsconfig) files. These files store the SQL Server connection string to the SSISManager configuration database. In production, the best practice is to have one XML configuration file on every server on which SSIS packages are run. The connection string is then used by all those packages on that server. It is also possible to have a centralized XML configuration file on a file share that can be accessed by all packages via a Universal Naming Convention (UNC) path. In our sample implementation, the XML configuration file is named SSIS_Configuration.dtsconfig file.
An environment variable. The environment variable points to the XML configuration file’s location, which can be a UNC path. The environment variable’s name is then hardcoded into each SSIS package. Setting the location through the environment variable allows the relocation of XML configuration files without any impact to the packages. The environment variable must be present on all servers on which SSIS packages are being executed. Figure 1 shows the creation of an environment variable named SSISConfiguration that points to the SSIS_Configuration.dtsconfig file.

Figure 1 Creating the environment variable that points to the XML configuration file
Figure 1 Creating the environment variable that points to the XML configuration file

Modifying the SSIS Package Template

After the configuration management framework is set up, the SSIS package template must be modified. We must enable the packages to use configurations in the Package Configuration Organizer dialog box, which can be accessed by through the Package Configurations option in the SSIS menu in Business Intelligence Development Studio (BIDS). As Figure 2 shows, the Enable package configurations check box must be selected, after which the following configurations can be entered:
  1. XML Configuration. This configuration is designed to read the SSISConfiguration environment variable to get the location and name of the .dtsconfig file and then read this file to retrieve the connection string to the SSISManager database.
    1. Common Configuration. This configuration connects to the SSISManager database and reads data from the SSISCommonConfigurations table, reading all rows that have a filter value of “Common Configuration”. This configuration assigns all connection strings and other shared configurations to package variables for use by package objects at runtime.
  2. Package Configuration. This is an optional configuration for reading package-specific configuration information. If included, it would connect to the configuration database and read data from a package-specific table.
Figure 2 Enabling package configurations in the SSIS package template
Figure 2 Enabling package configurations in the SSIS package template

Please note this strategy is applicable to SQL Server Integration Services 2005, 2008 and 2008 R2.   In relation to configuration management, there are significant changes which are expected in the next version of SQL Server. We will publish an article in a later release to highlight how to implement a good configuration management strategy in the Denali release of SSIS.

Conclusion

Implementing the three-pass configuration management solution will ensure that any change in the environment will be easily adapted by the SSIS packages at runtime and thus minimize errors. It will also simplify the management of those packages. In Part 3 of this series, we will learn how to standardize auditing and logging in order to provide consistent information in case of failures or for tuning.