Multiple Configuration Files

Now that you understand how to create and edit a configuration file, you are ready to learn how to create multiple configuration files. This section will discuss the other configuration types supported by SSIS, including the environment variable, registry entry, parent package variable, SQL Server table, and direct and indirect configuration types.

Environment Variable

An environment variable configuration sets a package property equal to the value in an environment variable. You must create and set the environment variable value by using the Microsoft Windows-based operating system. Only one package property can be configured per environment variable configuration. Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package. The environment variable contains only a value. The Package Configuration Organizer must contain information about the package property that is going to be set equal to the environment variable.

Registry Entry

A registry entry configuration is similar to an environment variable configuration. Registry entries are more difficult to create but more secure than environment variables.

Parent Package Variables

A parent package can contain an Execute Package task that executes a child package. The child package can contain a parent package variable configuration that sets a child package property equal to the value of a parent package variable. For example, a child package could extract data from the Employees table for one department and write it to a text file. A parent package could contain a Department variable that the child package reads to determine the set of employee data to write to the text file.

SQL Server Tables

SQL Server configurations contain both the package property path and the value. A single SQL Server configuration can contain multiple package properties and values. SQL Server configurations are useful for large-scale SSIS implementations and when a single configuration can be used by multiple packages. For example, you might have a configuration that contains your data warehouse server name and database name. Multiple packages could then read this configuration as they update the data warehouse.

Direct and Indirect Configurations

When you create a configuration in the Package Configuration Wizard, you can specify the location of the configuration (direct), or you can specify an environment variable that contains the location of the configuration (indirect). Indirect configurations can be created for XML configuration files, registry entries, and SQL Server configurations. Indirect configurations require one environment variable for each configuration, but they allow XML configuration locations to be changed without editing a package.