Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 28, 2010

The Advantages of Using Variables in SSIS Package Configurations

By Marcin Policht

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM