Package Configurations

Package configurations are a flexible method of dynamically configuring a package at runtime. This allows you a high degree of flexibility in the execution of SSIS packages. This allows you to design the package to run in different environments without having to modify the package file itself. When a package is written, not all operational parameters may be known, such as the location of a file or the value of a variable. By supplying this information at runtime, the user does not have to hard-code these values into a package. When a package is run, the values stored in the specified configuration store are loaded for use during the package's execution. The configuration capabilities of SSIS support the storage of data in five different data stores. The following list describes each type of data store and its capabilities:

• XML File Configuration: The XML File Configuration option stores package information in an XML file on the file system. This configuration provider lets you store multiple configuration settings in a single file. As an alternative to hard-coding the path to the XML file, the path can be stored in a user-defined environment variable. This option allows you to modify the XML file easily and distribute the configuration easily with the package.

• Environment Variable: The Environment Variable option allows you to store a configuration value in an Environment variable. This option will only allow you to save a single configuration parameter. By specifying an environment variable that is available on each machine the package will run on, you can be sure that the package configuration will be valid for each environment. Also, the setup of the environment variable can be done once during initial setup of package's environment.

• Registry Entry: The Registry Entry option allows you to store a configuration value in a registry value. Only a single value can be specified. Optionally, you can specify an environment variable that contains a registry key where the value is stored. Configuration entries in the registry are a secure and reliable way to store configuration values.

• Parent Package Variable: The Parent Package Variable option allows you to specify a fully qualified variable in a different package as the source for the configuration value. Only a single value can be stored in a specified configuration store. This is a good way to link packages and pass values between packages at runtime. If one package depends on the results from another package, this option is perfect.

• SQL Server: The SQL Server option creates a SSIS Configuration table in a database that you specify. Since this table could hold the configurations for multiple packages, a configuration filter value should be specified to allow the system to return the correct configuration values. This option allows you to specify multiple configuration values that will be stored under the filter name specified. Optionally, you can specify the database, table, and filter in an environment variable in the following format:

   <database connection>;<configuration table>;<filter>; 

  For example:

   VSTSB2.WroxTestDB;[dbo].[SSIS Configurations];Package1; 

Creating a Configuration

To create a configuration for a package, select SSIS Package Configurations. In the dialog that is displayed, select the "Enable Package Configurations" checkbox. From here, you must define which package configuration provider to use. This can be accomplished through the Package Configuration Wizard that is started when you click the Add button.

On the first page of the wizard, shown in Figure 16-6, you must decide which configuration provider you wish to use to store the configuration information. For this example, choose the XML File Configuration option. Now specify the path where the configuration file will reside. Having a standard location to store your configuration files will help ensure that as a package is moved from environment to environment, the links to the configuration will not be broken. If the path to the configuration is not standard, you can store the path to the configuration file in an environment variable and reference the environment variable in the package wizard. Remember, if you have recently added the environment variable to your system, you may need to reboot for it to be available for use in your package.

Figure 16-6

Once you've chosen a configuration storage provider, the next step is to specify the properties to save in the configuration store, as shown in Figure 16-7. You can either select a single value from the property tree view or select multiple values at one time. Because you selected the XML File Configuration provider, you can select multiple values to store.

Figure 16-7

Notice that not only can you store default values to load at the time the package is executed, but you can also load entire object definitions at runtime. This is useful if you just want to load a variable's value or actually specify an entire variable configuration at runtime. This would be useful if you wanted to configure the actual properties of a variable. Almost every aspect of a package can be persisted to a configuration store. These include package properties, configured values in defined tasks, configuration information for log providers, and Connection Manager information. About the only thing you can't store in a package configuration store is information about the package configurations.

Once finished, the package configuration information is stored in the package. When the package is executed, the configuration providers will load the values from the specified data stores and substitute the values found for the default values saved in the package.

Programming the Configuration Object

You can also programmatically configure a package's configuration through the Configuration object. This is useful if you would like to configure a package through managed code as shown at the beginning of this chapter. You could also configure a configuration file through managed code. All package configurations can be accessed through the Configurations collection of the package object.

The Configuration object exposes functionality to dynamically configure a package's configuration settings. This allows you to programmatically configure a package based on the environment in which it will run. Since a package can contain multiple configuration sources, you can discover all the configurations in a package by enumerating the configuration objects in the Configurations object.

Configuration Object

The Configuration object exposes the following members:

• ConfigurationString — The path describing where the physical configuration store is located.

• ConfigurationType — Sets the configuration provider to be used to interface to the configuration data store. The configuration type is referenced in from the DTSConfigurationType enumeration. Note that a DTSConfigurationType that starts with an "I" denotes that the configurationstring is stored in an Environment variable.

• Name — The unique name for the package.

• PackagePath — Defines the path of the actual data that is being accessed.

The following example details how to add an existing configuration store to a package. First, the EnableConfiguration property is set to true. Then, an empty configuration object is added to the package. The configuration object is then set to the Config File type, which directs the configuration to expect a valid dtsconfig file to be specified in the configurationstring property. Finally, the path to the configuration information is supplied and the package's path is stored. The package is then saved, thus persisting the configuration setup to the package file.

 public static void CreatePackageConfiguration() {     Application dtsapp = new Application();     string packagePath = @"C:\Package6.dtsx";     Package package = dtsapp.LoadPackage(packagePath, null);     package.EnableConfigurations = true;     Configuration config = package.Configurations.Add();     config.Name = @"ConfigurationMain";     config.ConfigurationType = DTSConfigurationType.ConfigFile;     config.ConfigurationString = @"C:\ConfigMain.dtsconfig";     config.PackagePath = package.GetPackagePath();     dtsapp.SaveToXml(packagePath, package, null); }