Let us start with reviewing the goals of a good configuration management solution:
- 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.
- 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.
- 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.
- 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.
- 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
Modifying the SSIS Package Template
- 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.
- 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.
- 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.
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.