The Advantages of Using Variables in SSIS Package Configurations

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