Understanding Package Configurations

Microsoft SQL Server 2005 Integration Services (SSIS) provides package configurations that you can use to update the values of properties at run time. A package configuration is a property and the value assigned to the property that you add to a completed package. This enables you to modify package property values without having to modify the actual package. Some examples of package configurations include file paths, user logon information, server names, database connections strings, variable values, and so on. A common use of package configurations is to enable the server name and user logon information to be applied dynamically at run time.

Configuration Benefits

Package configurations provide the following benefits:

• Configurations make it easier to move packages in development, test, and production environments. For example, a configuration can update the path of a source file or change the name of a database or server to a new resource location without modifying the actual package.

• 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 will 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.

Configuration Types

Before you can create a package configuration, you must specify the configuration type. SSIS supports several different methods of storing package configurations. Table 8-1 briefly describes the package configuration types supported by SSIS. In this section, you will learn how to configure an XML file. The following sections will show you how to configure environment variable, SQL Server, and parent package variable configurations.

Table 8-1: Package Configuration Types Supported by SSIS
Open table as spreadsheet

Configuration Type	Description
XML	An XML file can contain a single or multiple configurations.
Environment variable	An environment variable contains the configurations.
Registry entry	The registry entry contains the configurations.
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 can contain either single or multiple configurations.
Direct and indirect	SSIS provides direct and indirect configurations. Indirect configurations use environment variables.

Understanding the XML Configuration File

XML is the preferred format for a configuration file because it can be read by many different technologies. An XML configuration file can contain configurations for multiple properties. It contains both the property path (for instance, \Package.Connections[CustomerList].Properties[ConnectionString]) and the configured value (for instance, C:\Temp\Integration Services Project1\Data\CustomerList.txt). XML configurations are simple to read and modify. They are also easy to keep together with the package (*.dtsx) file.

Specifying a New XML Configuration File Location

If you use a direct XML configuration file and move your package to a new environment, you must edit the package to specify the new location of the XML configuration file. If you use indirect configurations, you must specify one environment variable for each configuration. Using direct and indirect configurations are discussed later in this chapter.