Package Configurations

Packages are a collection of objects with properties. The values of some of those properties reflect the environment in which the package runs. For example, tasks need to open files and so need the name of the file to open. Connections need to connect to servers and, therefore, need the connection information for the servers. Each package is different, of course, and not every package has the same type of location dependence. But almost all packages have a reference to some resource such as a file or server that becomes invalid if you move it to another machine.

Another issue that causes long-term package management problems is the reality that the environment in which the package runs will change over time. If the environment never changed, location dependence and resource references wouldn't be a problem. All the environment-specific values could be set once and forgotten. But machines do change, filenames do change, and new servers do replace retired servers. Because the environment changes, your packages need to be edited to adjust property values to reflect the changes so the packages will continue to function correctly.

So, there are really two classes of problems here: those modifications required as a result of moving the package from one machine to another and those caused by environment changes over time. In both cases, you need to modify the package to account for the differences. But, there are also problems associated with editing packages: It can be time consuming, error prone, or against security policies. You need a way to quickly modify packages in a centralized way without physically opening and editing the package directly. This is the purpose of package configurations.

How Package Configurations Work

All package configurations have two essential pieces of information:

• A value for a given property on an object in the package

• The package path to the property of the object

The property values are stored as strings in package configurations. If the property is an integer or other nonstring type, the SSIS runtime converts the value to the correct type before setting the property value. If the SSIS runtime cannot convert the string value to the property's type, you receive a warning when loading the package indicating that the configuration failed.

The path to the property inside the package is stored in what is called a package path. Because a package is a hierarchical collection of containers, tasks, and other components, it is possible to uniquely address almost any property on any object in the package using a package path.

Caution

"Almost any" property is stated because there are some properties that are not visible to the SSIS runtime. For example, the Data Flow Task only exposes certain key component properties. Some properties on the FTP and HTTP Connection Managers are not available on the connection managers but are only provided on their connection subobjects. As a rule, if an object provides a way to associate a property expression with a property, you can also address the property with a package path.

Package Paths

Package paths are essentially resource names that point to a property on an object in the package. Here are some examples:

\Package.Connections[{E9598474-461E-48F7-B902-52A140B7FE14}].ConnectionString 

This package path points to the ConnectionString property on the ConnectionManager using the GUID ID as the index.

\Package.Connections[ConnectionName].ConnectionString 

This package path points to the ConnectionString property on the same ConnectionManager but uses the ConnectionManager name ConnectionName as the index instead of the GUID ID.

\Package\LoopOverFiles\sql-TruncateLoadTable.SqlStatementSource 

This package path points to the SqlStatementSource property of the Execute SQL Task in the Foreach Loop Container named LoopOverFiles.

\Package\Foreach Loop Container.ForEachEnumerator.Properties[FileSpec] 

This package path points to the FileSpec property of the Foreach File Enumerator of the Foreach Loop.

\Package.Variables[FileLocation].Value 

This package path points to the Value property of the FileLocation variable.

\Package\Data Flow Task.Properties[[DataReader Source].[SqlCommand]] 

This package path points to the SqlCommand property of the DataReader Source component in the Data Flow Task.

Caution

Package configurations physically change packages. When you apply a configuration to a package, it changes the values of variables and properties in the package as though you had opened the package in the designer and typed them in directly. If you save a package after changing properties and variable values with a configuration, it contains the configured property and variable values the next time you load the package.

As you can see from the preceding examples, package paths are composed of the \Package marker, container names, collection element names, and property names.

• All package paths start with the \Package marker.

• The '.' character indicates that a property follows.

• The '\' character indicates that a container or task follows.

• The string between the [ and ] characters indicates an index into a collection.

Tip

When creating configurations, the Package Configuration Wizard by default uses an Objects GUID ID when building the package path. You can also use an object's name in a package path. The ID is safer to use because SSIS never changes IDs. Unless someone manually edits the ID in the package, the ID never changes. If you use the object's name, and someone changes the name during the course of developing the package, the configuration is no longer valid for that object. However, using the name is more portable. For example, a configuration with the previous fourth package path could be applied to all packages that have a variable named FileLocation in the package's variable collection (defined at package scope).

Package Configuration Object

Each entry in the package's Configurations collection has the properties discussed in the following sections.

ConfigurationType

The ConfigurationType property on configuration objects specifies the package configuration type. Although there are only six base types, there are also indirect variants, as described in Table 14.1.

Table 14.1. Configuration Type Identifiers

Configuration Type	Identifier	Description
PARENTVARIABLE	0	Parent package variable for setting values of variables in subpackages from parent package variable values
CONFIGFILE	1	XML file configuration
ENVVARIABLE	2	Environment variable configuration; contains one configuration
REGENTRY	3	Registry entry configuration; contains one configuration
IPARENTVARIABLE	4	Indirect parent package variable
ICONFIGFILE	5	Indirect XML file configuration
IREGENTRY	6	Indirect Registry configuration
SQLSERVER	7	SQLServer table configuration
INIFILE	8	INI file configuration (deprecated)
ISQLSERVER	9	Indirect SQL server table configuration
IINIFILE	10	Indirect INI file configuration (deprecated)

Note

There's no need to have an indirect configuration type for an environment variable configuration because they already are, in a sense, indirect configurations.

ConfigurationVariable

This is now a misnomer and is an artifact of an earlier design that supported configuring variables exclusively. It should probably be called ConfigurationPackagePath. It contains the package path for the property to be configured. For configuration types that have more than one configuration, such as SQL table configurations and XML configurations, this value is empty because the package paths are provided in the configuration itself.

Tip

To see the configurations in the XML package, load the package in BIDS, right-click on the package in the Solution Explorer, and select View Code. If you've created a configuration, toward the top of the package XML, you should see some XML that looks similar to the following:

<DTS:Configuration>    <DTS:Property DTS:Name="ConfigurationType">       2    </DTS:Property>        <DTS:Property DTS:Name="ConfigString">       SSISSAMPLESROOT    </DTS:Property>    <DTS:Property DTS:Name="ConfigurationVariable">       \Package.Variables[SAMPLES::ROOTWD].Properties[Value]    </DTS:Property>    <DTS:Property DTS:Name="ObjectName">       Configure The Root Folder    </DTS:Property>    <DTS:Property DTS:Name="DTSID">       {90405045-3A91-43C2-B759-6C183C0E81A6}    </DTS:Property>    <DTS:Property DTS:Name="Description">    </DTS:Property>    <DTS:Property DTS:Name="CreationName">    </DTS:Property> </DTS:Configuration> 

ConfigString

The ConfigString value contains the information needed to acquire the configuration. For example, for XML configurations, the ConfigString contains the fully qualified path to the XML configuration file, whereas for environment variable configurations, this property contains the name of the environment variable where the configuration is stored.

For indirect configurations, the ConfigString property contains the name of the environment variable that contains the Configuration String for the configuration. For example, for indirect XML configurations, the ConfigString property contains the name of the environment variable that holds the fully qualified path to the XML configuration file.

Indirect Configurations

In the same way that directly specifying the location of a resource is problematic, directly specifying the location of a configuration can be equally problematic. Suppose you create a package with one XML file configuration. In the configurations, you point to the location of the XML file configuration in your D:\Configurations directory. When you move the package to a new machine without a D drive, you're still forced to change the package to account for the differences in available hard drives. These situations make it necessary to have a way to configure the configurations. To do this, Integration Services has what is called indirect configurations.

Indirect configurations use environment variables to store the ConfigString of the configuration and are available for all configuration types except environment variable configurations. When using indirect configurations, the value that you would normally place in the ConfigString property of the configurations goes in the environment variable value and the name of the environment variable goes in the ConfigString property of the configuration.

Note

Why are fully qualified directories necessary? Relative paths would make packages more flexible, right? The reason is because relative paths are relative to the package execution location. Because packages can be executed from different utilities and applications, there's no guarantee that the relative path will correctly address a given file. This is one reason IS provides indirect configurations, so that the path can be fully qualified, yet still abstract away environmental differences.

Applying Package Configurations

Packages have a property called Configurations, which is a collection of zero to many Package Configuration objects. When you create a package configuration using the Package Configurations Organizer, you are adding an element to the package's Configurations collection. The SSIS runtime stores the configurations information along with the package when you save the package to SQL Server or an XML package file.

When you load the package, the package reads in all the package configurations and applies them in the order in which they were stored in the collection. It's possible to have more than one configuration and more than one type of configuration in the same package. It's also possible to configure the same property with more than one configuration. The last configuration to be applied winsthat is, the configured property retains the value from the most recent configuration.

Caution

The load-time behavior of package configurations has confused some users who attempt to change the configuration values and then expect to see the property values change in the package immediately. To see the effects of configurations, you must reload the package.