Implementing SSIS Package Configurations

Monday Jul 26th 2010 by Marcin Policht

Marcin Policht demonstrates how to leverage SQL Server Integration Services (SSIS) package variables in order to modify SSIS properties without directly editing package content.

Marcin Policht demonstrates how to leverage SQL Server Integration Services (SSIS) package variables in order to modify SSIS properties without directly editing package content.

How to Implement Package Variables in SQL Server 2008 illustrated how to leverage package variables in order to promote code reusability. This was accomplished by replacing hard-coded values assigned to properties of individual package components (such as database connection strings or file paths in data flow sources and destinations as well as criteria used to evaluate output of the Conditional Split Transformation) with user defined variables. Now it is time to extend this concept by demonstrating different ways of modifying the values of these properties without directly editing package content. We will start by presenting an approach that relies on package configurations (whose general characteristics were covered in The Advantages of Using Variables in SSIS Package Configurations).

In this presentation, we will continue working with our sample package, intended to extract a subset of rows from the vEmployee view in the AdventureWorks database based on the match on the City field. The package consists of five components - OLE DB Connection with the corresponding OLE DB Source providing access to the data source, Conditional Split Transformation redirecting rows matching our criteria to a designated output, and Flat File Connection Manager with its Flat File Destination where the results are stored. The first of them has its connection string represented by an expression containing srcServer and srcDatabase variables with the package scope residing in the User namespace (taking the form of @[User::srcServer] and @[User::srcDatabase], respectively). Similarly, Flat File Connection Manager relies on the dstFilePath User variable (in the format @[User:dstFilePath]) to identify the location and name of the output file. Finally, our criterion defined in the Conditional Split Transformation is based on comparison against a value of the City variable residing in the User namespace and scoped on the Data Flow Task level (and expressed as @[User:City]).

While you can change the values of each of these variables relatively easily from the Business Intelligence Development Studio (via its Variables floating window), such action involves package modifications, which introduces maintenance overhead. However, this extra step can be avoided by taking advantage of package configurations (as we will explain later, there are also other ways of accomplishing the same goal).

As we have described earlier, SQL Server 2008 Integration Services supports five different configuration types, categorized (according to the storage type each of them depends on) as XML configuration file, registry entry, parent package variable, SQL Server, and environment variable. Selecting the most appropriate one (and implementation details that follow) would depend on your specific requirements; however the basic principle in each case remains the same. In our demonstration, for the sake of simplicity, we will first present the most straightforward one, which utilizes environment variables.

Start by defining environment variables that target the same configuration settings, which were previously implemented with the help of package variables. This is most commonly done using the Environment Variables dialog box, accessible via the Advanced tab of the System Properties window (to display it, activate System applet in the Control Panel). Click on the New... command button in its lower section (containing the list of System variables) to activate the New System Variable dialog box. Type in srcServer it its upper textbox, labeled Variable name (note that our choice of environment variable name is intended to reflect its relationship with the package variable, however, this decision is arbitrary and no specific naming convention is required in this case), followed by the name of the target SQL Server instance underneath (specifying this way the Variable value). Click on OK to finalize both entries. Repeat the same sequence of steps for srcDatabase (set to AdventureWorks), dstFilePath (representing location and name of the output file), and City (representing the city where employees we are interested in reside).

Now it is time to open our sample project in the Business Intelligence Development Studio (it is critical that this happens after the system variables have been defined and their values have been set). Ensure that the package content appears in the Designer window and choose the Package Configurations... entry from the SSIS top level menu. In the resulting Package Configuration Organizer, click on the Enable package configurations checkbox, followed by the Add... command button to invoke Package Configuration Wizard. On its initial page, labeled Select Configuration Type, choose the Environment variable entry in the Configuration type listbox and select the first of our variables (srcServer) in the Evironment variable textbox underneath (alternatively, you have an option to select one of the existing environment variables on the local computer). Click on Next command button and, on the Select Target Property page, traverse through the hierarchy of nodes in the Objects left window pane down to Value property of srcServer entry under Package -> Variables subnode. (Note that it is also possible to directly target individual properties of connection managers, executables, such as data flow and control flow tasks, as well as the package itself). Click on Next to get to the Completing the Wizard page and assign a meaningful name to the newly defined configuration. You will also find a preview of the currently defined settings here, including Target Property (which we will discuss in more detail shortly). Repeat the same sequence of steps, creating additional configurations utilizing srcDatabase, dstFilePath, and City environment variables referencing package variables with matching names (in the case of City, you will need to drill down the Executables -> Data Flow Task -> Variables node).

If you trigger package execution at this point, the Output window should display four informational messages stating that The package is attempting to configure from the environment variable (respectively for "srcServer", "srcDatabase", "dstFilePath", and "City"), leading to successful completion, resulting in the creation of the file whose path you defined containing data for employees residing in the city of your choice.

Environment variable based configurations, while straightforward to implement, are relatively inflexible. In particular, as you noticed, you need to create a separate configuration for each package property or variable. In addition, there is a timing dependency, which forces you to reload the Business Intelligence Development Studio if you want it to recognize creation of a new environment variable or change of an existing one (environment variables are read only during its startup). Finally, there is no straightforward way to arbitrarily designate configurations that a package to be executed should be working with. If these shortcomings are relevant to you, you might want to consider employing a different configuration type, such as the one based on XML configuration file.

In this case, after clicking on the Add... command button in the Package Configuration Organizer dialog box, you would accept the default XML configuration file entry in the Configuration type listbox and either point to the corresponding file directly (in the Configuration file name textbox) or store its location and name in an environment variable, which name you specify in the listbox located next to the Configuration location is stored in an environment variable label. To eliminate dependency on environment variables, we will use the first approach. After providing the full path to the file, click on Next command button. On the Select Properties to Export page of the Package Configuration Wizard traverse the hierarchy of entries in the Objects left window pane, enabling checkboxes next to Package -> Variables -> srcServer -> Value, Package -> Variables -> srcDatabase -> Value, Package -> Variables -> dstFilePath -> Value, and Package -> Executables -> Data Flow Task -> City -> Value. On the Completing the Wizard page, verify that all of the selected properties appear in the Preview window and assign a meaningful name to your custom configuration.

The resulting XML file stored in the location you specified has the default dtsConfig extension (which can be changed, if desired) and consists of a number of elements (matching the number of individual properties you designated when running the Package Configuration Wizard). Its content can be safely modified outside of Business Intelligence Development Studio, using Notepad.exe or any XML editor. This typically involves changing the subnode within an appropriate element (we will look in more detail into its structure in our upcoming article). More importantly, such changes are dynamically recognized, taking effect as soon as the file is saved and the package reloaded (eliminating the need to restart Business Intelligence Development Studio). Similar behavior is exhibited by the registry entry and SQL Server configurations (Parent Package Variable-based configurations are even more flexible, making it possible to apply changes as the child package starts executing, rather than at the parent package load time, as is the case with other configuration types).

Additional Resources

SQL Server 2008 R2 Index
MSDN Using Variables in Packages
MSDN Integration Services Variables

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site