The Advantages of Using Variables in SSIS Package Configurations


SQL Server 2008 Integration Services primarily serve as a development platform for automating Export, Transformation, and Loading (ETL) tasks. The benefits of using variables to promote code reusability can be extended by taking advantage of package configurations. In this article, we will introduce package configuration characteristics.

As we have recently pointed out, SQL
Server 2008 Integration Services serves primarily as a development platform for
automating Export, Transformation, and Loading (ETL) tasks. Consequently,
mechanisms that pertain to software programming should be taken into account
when creating SSIS packages. We have already described how this rationale
applies to the use of variables in promoting code reusability. However, benefits
of such an approach can be extended even further by taking advantage of package
configurations. In this article, we will introduce their basic characteristics.

It is quite clear that variables play a significant
role in modularizing code, eliminating repetitiveness, and simplifying edits.
Unfortunately, making changes to existing packages still involves opening them
in Business Intelligence Development Studio, adjusting
variable values, and recompiling the modified code. All of these extra steps
can be eliminated by taking advantage of package configurations. These
constructs, introduced in SSIS 2005 (based on functionality incorporated into Dynamic Properties Task in SQL Server
2000 Data Transformation Services) facilitate changes to package components
(including their properties and variables) based on the content of external
data sources (such as XML files, registry entries, database tables, environment
variables, or parent package variables). In addition, to further increase
flexibility of a configuration, you have the option of storing its location in
an environment variable (with the obvious exception of the Environment variable configuration
type). This allows you to alter it afterwards in the least intrusive manner, by
simply assigning another value to that environment variable (rather than being
forced to modify an existing file, SQL Server database table, registry entry,
or parent package variable). It is important to note that configuration changes
(with the exception of those based on parent package variables) are not applied
dynamically (during runtime), but instead are introduced during package load
(when package execution is invoked).

To implement a configuration, launch Business
Intelligence Development Studio.
With the Designer
workspace open and active, select the Package Configurations... item from the SSIS top level menu
(alternatively, you can locate that item in the context-sensitive menu while
right-clicking on the Designer surface). In the resulting Package Configurations Organizer window (whose layout
clearly indicates that it is possible to have multiple configurations hosted
within the same package), mark the Enable package configurations checkbox and
click on the Add... command button.
This will trigger the Package
Configuration Wizard
, which guides you through the process of creating a
configuration. On the Select
Configuration Type

page, you need to select one of five available listbox entries including:

  • XML configuration file – XML
    configuration file
    is convenient due to its portability. (You have the ability to duplicate
    configuration settings by simple file copy). Implemented as an
    XML-formatted file (with the default .dtsconfig extension), it is capable
    of storing values of multiple properties and variables. Once you provide
    the location and name of the file (on the Select Configuration Type page) and
    specify the settings it should contain (on the Select Target Property page) the
    wizard will automatically generate it and populate its content. To assign
    arbitrary values to properties and variables stored in the XML
    configuration file, edit it directly via a text- or an XML-editor. This
    involves modifying the element of a target SSIS object identified by the
    element. (We will describe its format in more detail in our next article).

  • Registry entry – Similar to
    the XML configuration file, the registry entry is relatively easily
    duplicated across multiple computers, although it requires registry
    modifications. It is intended for hosting values of individual variables
    or properties. The content of the Registry entry text box
    on the Select Configuration Type page of the Package
    Configuration Wizard
    designates a key under HKEY_CURRENT_USER registry hive. (This designation
    is implicit, so it should not be specified). Configuration mechanism
    utilizes the Value entry
    under that key (also implied and not included in the text box) to populate
    the corresponding variable or property. For example, if we wanted to
    assign a specific value to the ConnectionString property
    of a connection manager in a sample package, we could accomplish this by
    creating HKEY_CURRENT_USERSoftwareACMESIS
    ConfigurationsPkg0001DBConnectionString
    key
    containing a single Value
    entry or REG_SZ data type
    (which would contain the appropriate database connection string). In this
    case, the Registry entry text box should be set to SoftwareACMESIS
    ConfigurationsPkg0001DBConnectionString
    .

  • Parent package variable – Parent
    package variable
    is geared specifically towards scenarios involving parent and child
    packages. Like Registry entry-based configuration, it
    facilitates assigning values to individual SSIS components, but uses a
    variable defined in the parent package (identified based on the content of
    Parent variable textbox on
    the Select Configuration Type page of the Package
    Configuration Wizard)
    for this purpose. As mentioned earlier, this assignment takes place during
    package execution, which differentiates this configuration type from
    others (since they carry out changes during load time, when package
    execution is invoked).

  • SQL Server – SQL Server is well suited
    for more complex scenarios, with a large number of configurations and
    packages. In addition, it accommodates security model, in which access to
    packages and their configuration settings need to be controlled on the SQL
    Server level. Similarly to XML configuration file,
    it is capable of hosting multiple property and variable values, which are
    stored in a SQL Server database table consisting of the following columns:

    PackagePath NVARCHAR(255)
    NOT NULL, ConfiguredValue NVARCHAR(255) NULL, ConfiguredValueType NVARCHAR(20)
    NOT NULL ConfigurationFilter NVARCHAR(255) NOT NULL 

    PackagePath identifies the
    SSIS object that is being configured (you will find out more about its format
    in our next article). ConfiguredValue provides the value that should be
    assigned to that object. ConfiguredValueType designates its data type. ConfigurationFilter stores an
    arbitrary character string that allows you to separate configuration settings
    that you intend to apply in a given scenario from others that you want to
    exclude. This filtering action is activated by selecting the entry matching
    that string in the Configuration filter listbox on the Select Configuration
    Type page.

    The table can be
    created manually as long as its structure follows the format described above.
    Alternatively, it is possible to generate it automatically by leveraging the Create
    Table
    dialog box, accessible via the New… command button next to the Configuration
    table
    listbox on Select Configuration Type page of the Package
    Configuration Wizard.
    On the same page, you also need to define the connection to the database where
    the table is stored. In this case, you also have an option to leverage existing
    connection managers or create a new one using the Connection Manager dialog box
    (accessible via New…->Configure OLE DB Connection Manager).

  • Environment variable – Environment
    variable
    is easy to configure and modify. Unlike XML configuration file or SQL
    Server,
    it is intended for storing a single property or variable value. When
    operating in an Active Directory environment, you have the ability to
    manage environment variables across multiple computers by leveraging Group
    Policies and Group Policy Preferences. To modify them directly, use the Environment
    Variables
    dialog box, accessible by clicking on the Environment Variables… command
    button on the Advanced tab of the System Properties dialog box
    (to display it, navigate through Control Panel -> System and
    Security -> System -> Advanced System Settings
    links). In
    general, you should use system (rather than user) variables.

    Keep in mind
    that new environment variables become available only to processes that were
    initiated following their creation. Effectively, this implies that you will
    need to re-launch Business Intelligence Development Studio if you want to
    be able to see them in the Environment variable listbox on the Select
    Configuration Type
    page of the Package Configuration Wizard. In addition, note that typing a new
    value in that listbox will not automatically generate a corresponding
    environment variable.

In the case of the first four
configuration types listed above, you have the ability to specify their
settings directly by providing (respectively) the configuration file name,
registry entry, parent variable, or SQL Server connection and configuration
table on the Select Configuration Type page of the Package
Configuration Wizard.
Alternatively, you can specify their setting indirectly by storing configuration
location in an arbitrary environment variable, which corresponds to the Configuration
location, is stored in an environment variable selection on the same page.
Understandably, for the last configuration type (Environment variable), the second of
these two options is not available.

Once you chose the configuration type
and advance to the next step of the wizard, the interface you are presented
with depends on the option that you selected on the previous page. If you
decided to use direct assignment, then you will be presented with the Select
Target Property

page, where you are either limited to selecting a single property or variable
(when working with Registry entry, Parent package variable, or Environment
variable)
or are allowed to choose multiple ones (with XML configuration file and SQL
Server).
If you decided to use indirect assignment, no further steps are required
(instead, you simply need to assign the configuration name and confirm your
selection by clicking on the Finish button on the Completing the
Wizard
page).

Additional Resources

MSDN Package Configurations

MSDN Understanding Integration Services Package Configurations

MSDN Creating Package Configurations

»


See All Articles by Columnist

Marcin Policht

Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles