SSIS provides flexible and varied methods for configuring its packages. These allow operators or developers to change a package’s behaviour at run time to adapt to changing environments.
While SSIS is flexible, I personally found there was a lack of guidance as to the best choices. It took some time and a good deal of trial and error to find techniques that worked best. This article aims to distil that experience.
Only Create Configurations for Changeable Items
Create configurations for:
- Server names. For instance database, smtp and ftp servers.
- File shares. (e.g. \\myserver\myshare)
- Operator parameters
Use configurations for items that are likely to change when the operating environment changes. For instance when a system is moved to test or production or when a server name changes.
Do not create configurations for items that could conceivably change but are unlikely to do so. For instance there would normally be no need to create configurations for database names or the relative paths to individual files.
Paths to Files should be an Expression
Usually a connection manager for a file path should use an expression referencing a configurable file share concatenated with a relative path.
Configurations should be re-used
When a number of SSIS packages reference the same servers and file shares, then the configurations of these packages should be coordinated. This should be done such that each logical server/file share has a single configuration that applies to all relevant packages. Note the term logical server. Sometimes a single server can be used for multiple purposes – for instance to house sales data and logging data. In this case a call must be made on the likelihood of logging and sales being on the same server in all environments. If it is likely that these will be deployed on separate servers in one environment (e.g. test), then there should be a separate logical sales server and logging server.
Avoid creating lots of logical servers – only do so if it is probable that a separate server will be required.
This requires planning but pays off in simpler management of the packages at execution time
It is usually better to use configurations to set variable values rather than setting the connection manager properties directly.
Each configuration should set the value of a variable. This allows multiple connection managers to use the configured variable. For instance multiple file connection managers can share a common root file share via a variable. Similarly multiple database connections of different types can share the same server name via a variable.
Choose the Name with Care
The name of the variable being configured should be chosen with care. The name is global to the entire suite of packages. Do not use the physical server name. Use a logical name where possible. For instance use the name SalesReportingServer. Do not use the name Dev09.
Use Matching Names
Where possible the internal name of the configuration, the variable name and the external name of the configuration should match. This allows people maintaining packages to quickly identify and line up configurations.
The internal name if a configuration is the name given to a configuration within a package. By the external name I mean the name of the filter for an SQL configuration or the name of the Xml file for an Xml configuration.
Choosing between Xml, SQL and Environment Configurations
SSIS supports 4 main types of configurations:
- Xml files
- SQL Server
- Environment variables
- Parent Package
There are many options, and combinations.
One problem with configurations is that if they are used in an uncontrolled way they can become confusing. Its best to set and stick to some simple standards.
A simple scheme that I have found works well is to use XML configurations located in a standard folder on the C: drive. (say C:\SSISConfig). The C: drive is chosen because its generally available on servers as well as developers machines.
A slightly more sophisticated scheme I have used for one client is to use a combination of SQL Server configurations and environment variable configurations. The location of the SqlServer (server and database) containing the main configurations is set by environment variable configurations.
Use Parent Package Configurations for Passing Parameters
Parent package configurations allow a child package value to be overridden by the calling parent package. This mechanism allows parent packages to pass “parameters” to children. For instance a child package may export all data for a particular catalog and so takes the catalog ID from the parent. The parent package may have a loop and iteratively call the child passing different values for the catalog ID.
Parent configurations should not generally be used to set connection manager properties, such as server, database and file shares. One reason to avoid using parent package configuration for connection manager properties is that validation occurs before parent package configurations are set, so validation errors could occur in the child package before the “correct” value comes from the parent.