Implementing SSIS Package Configurations

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