Share via

Package Configurations

  • Article

SQL Server 2005 Integration Services (SSIS) provides package configurations that you can use to update the values of properties at run time. A configuration is a property/value pair that you add to a completed package. Typically, you create a package set properties on the package objects during package development, and then add the configuration to the package. When the package runs, it gets the new values of the property from the configuration. For example, by using a configuration, you can change the connection string of a connection manager, or update the value of a variable.

Package configurations provide the following benefits:

  • Configurations make it easier to move packages from a development environment to a production environment. For example, a configuration can update the path of a source file, or change the name of a database or server.
  • Configurations are useful when you deploy packages to many different servers. For example, a variable in the configuration for each deployed package can contain a different disk space value, and if the available disk space does not meet this value, the package does not run.
  • Configurations make packages more flexible. For example, a configuration can update the value of a variable that is used in a property expression.

Integration Services supports several different methods of storing package configurations, such as XML files, tables in a SQL Server database, and environment and package variables.

Each configuration is a property/value pair. The XML configuration file and SQL Server configuration types can include multiple configurations.

The configurations are included when you create a package deployment utility for installing packages. When you install the packages, the configurations can be updated as a step in the package installation.

Note

To become better acquainted with the concepts explained in this section, see Deploying Packages Tutorial and Lesson 3: Adding Package Configurations of Creating a Simple ETL Package Tutorial.

Package Configuration Types

The following table describes the package configuration types.

Type Description

XML configuration file

An XML file contains the configurations. The XML file can include multiple configurations.

Environment variable

An environment variable contains the configuration.

Registry entry

A registry entry contains the configuration.

Parent package variable

A variable in the package contains the configuration. This configuration type is typically used to update properties in child packages.

SQL Server table

A table in a SQL Server database contains the configuration. The table can include multiple configurations.

XML Configuration Files

If you select the XML configuration file configuration type, you can create a new configuration file, reuse an existing file and add new configurations, or reuse an existing file but overwrite existing file content.

An XML configuration file includes two sections:

  • A heading that contains information about the configuration file. This element includes attributes such as when the file was created and the name of the person who generated the file.
  • Configuration elements that contain information about each configuration. This element includes attributes such as the property path and the configured value of a property.

The following XML code demonstrates the syntax of an XML configuration file. This example shows a configuration for the Value property of an integer variable named MyVar.

<?xml version="1.0"?>
<DTSConfiguration>
   <DTSConfigurationHeading>
      <DTSConfigurationFileInfo
          GeneratedBy="DomainName\UserName"
          GeneratedFromPackageName="Package"
          GeneratedFromPackageID="{2AF06766-817A-4E28-9878-0DE37A150648}"
          GeneratedDate="2/01/2005 5:58:09 PM"/>
   </DTSConfigurationHeading>
   <Configuration ConfiguredType="Property" Path="\Package.Variables[User::MyVar].Value" ValueType="Int32">
      <ConfiguredValue>0</ConfiguredValue>
   </Configuration>
</DTSConfiguration>

Registry Entry

If you want to use a registry entry to store the configuration, you can either use an existing key or create a new key in HKEY_CURRENT_USER. The registry key that you use must have a value named Value. The value can be a DWORD or a string.

If you select the Registry entry configuration type, you type the name of the registry key in the Registry entry box. The format is <registry key>. If you want to use a registry key that is not at the root of HKEY_CURRENT_USER, use the format <registry key\registry key\...> to identify the key. For example, to use the MyPackage key located in SSISPackages, type SSISPackages\MyPackage.

SQL Server

If you select the SQL Server configuration type, you specify the connection to the SQL Server database in which you want to store the configurations. You can save the configurations to an existing table or create a new table in the specified database.

The following SQL statement shows the default CREATE TABLE statement that the Package Configuration Wizard provides.

CREATE TABLE [dbo].[SSIS Configurations]
(
ConfigurationFilter NVARCHAR(255) NOT NULL,
ConfiguredValue NVARCHAR(255) NULL,
PackagePath NVARCHAR(255) NOT NULL,
ConfiguredValueType NVARCHAR(20) NOT NULL
)

The name that you provide for the configuration is the value stored in the ConfigurationFilter column.

Direct and Indirect Configurations

Integration Services provides direct and indirect configurations. If you specify configurations directly, Integration Services creates a direct link between the configuration item and the package object property. Direct configurations are a better choice when the location of the source does not change. For example, if you are sure that all deployments in the package use the same file path, you can specify an XML configuration file.

Indirect configurations use environment variables. Instead of specifying the configuration setting directly, the configuration points to an environment variable, which in turn contains the configuration value. Using indirect configurations is a better choice when the location of the configuration can change for each deployment of a package.

See Also

Tasks

Creating Package Configurations

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Added link to tutorial that demonstrates how to create and use package configurations in a deployment.

14 April 2006
Changed content:
  • Added information about how to use registry entries to store configurations.