Package Configurations Part I - SSIS

Prior to SQL Server 2012, a package configuration was the optimal method to store configurable values that could be used in your package without altering the package. The introduction of parameters, which you can read about in Using Variables, Parameters, and Expressions Topic and see in the earlier examples in External Management and WMI Task Implementation Topic, means they are now the preferred method of storing configurable values that can be injected into packages at runtime.

Package configurations are still a valid way to store configurable values in other locations where parameters cannot be stored, such as XML files, SQL Server tables, and registry entries.

Package configurations are a flexible method of dynamically configuring a package at runtime. This gives you a high degree of flexibility in the execution of SSIS packages, enabling 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 hardcode 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 enables you to store multiple configuration settings in a single file. As an alternative to hardcoding the path to the XML file, the path can be stored in a user-defined environment variable. Using this option, you can easily both modify the XML file and distribute the configuration with the package.

>> Environment Variable: This option enables you to store a configuration value in an environment variable. Using this option, you are allowed to save only a single configuration parameter. By specifying an environment variable that is available on each machine on which the package will run, you can ensure that the package configuration is valid for each environment. Also, setup of the environment variable can be done once during the initial setup of the package’s environment.

>> Registry Entry: This 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: This option enables 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. When one package depends on the results from another package, this option is perfect.

>> SQL Server: This option creates an SSIS Configuration table in a database that you specify. Because this table could hold the configurations for multiple packages, a configuration filter value should be specified to enable 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:

;;;
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 below screenshot, you must specify 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 helps to ensure that as a package is moved from environment to environment, the links to the configuration are not broken. If the path to the configuration is not standard, you can store the path to the configuration file in an environment variable and then reference the environment variable in the package wizard. Remember that if you have recently added the environment variable to your system, you may need to reboot in order for it to be available for use in your package.

After you have chosen a configuration storage provider, the next step is to specify the properties to save in the configuration store, as shown in below screen shot. 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.

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. For example, you may want to configure the actual properties of a variable. Almost every aspect of a package can be persisted to a configuration store, including 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 specific data about the package configurations.

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

 

Frequently Asked SSIS Interview Questions & Answers

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 External Management and WMI Task Implementation Topic. 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 enables you to programmatically configure a package based on the environment in which it will run. Because a package can contain multiple configuration sources, you can discover all the configurations in a package by enumerating the Configuration objects contained in a PackageConfigurations property.

Configuration Object

The Configuration object exposes the following members:

• ConfigurationString: This is the path describing where the physical configuration store is located.
• ConfigurationType: This sets the configuration provider to be used to interface to the configuration data store. The configuration type is referenced from the DTSConfigurationType enumeration. Note that a DTSConfigurationType that starts with an “I” denotes that the configurationstring is stored in an environment variable.
• Name: This is the unique name for the configuration object in the package.
• PackagePath: This defines the path of the actual data that is being accessed.

The following example (see the CSharp_ConfigPkg folder) 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.

If you run this code against the Test SSIS package for External Management and WMI Task Implementation Topic, you’ll see a new myTestSSISPackageConfig.xml file in the TestSSISPackage directory with the additional variable that was added and a configuration for the variable.

This section has described how you can use the DTS runtime code library to perform many of your mundane administrative tasks programmatically. The next section looks at another feature of SSIS that you can use in your administrative Toolbox — Windows Management Instrumentation.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator