16 March 2009

Working with Property Expressions in SQL Server Integration Services

Property Expressions in SSIS packages are assigned to a property at run time. This allows parameters to be passed to a package every time they are run, making them far more versatile. Despite their obvious usefulness, they are not well known. Hopefully, Bob's article puts things right.

SQL Server Integration Services (SSIS) provides a number of advanced features that support the efficient development and deployment of SSIS packages. One of these features is the property expression, which lets you dynamically update a property value at runtime. For example, you can use a property expression to define the connection string of an OLE DB connection manager. That way, the connection to the data source can be generated dynamically at run time, letting you connect to different data sources without needing to reconfigure your package each time the data source changes.

In this article, I explain how to use property expressions in your SSIS packages and provide an example package that includes several property expressions. This package-PropertyExpressions.dtsx-includes a simple control flow and data flow that retrieve metadata from a local instance of SQL Server. Figure 1 shows the package’s control flow.


Figure 1: Control Flow in the PropertyExpressions.dtsx SSIS package

The control flow components perform the following tasks:

  • Execute SQL task: Queries the sys.databases catalog view in the master database to retrieve a list of database names and add them to the Databases variable.
  • Foreach Loop container: Loops through the list of databases in the Databases variable and, with each loop, assigns the current database name to the Database variable. The Foreach Loop container runs the following control flow tasks with each loop:
    • Execute SQL task: Queries the sys.databases catalog view in the master database to retrieve the database ID for the current database (in the Database variable).
    • Data Flow task: Retrieves a list of tables in the current database.

Notice also in Figure 1 that the PropertyExpressions.dtsx package includes two connection managers:

  • SqlSrv2005: Connects to the master database in the local instance of SQL Server 2005.
  • DatabaseTables: Connects to the C:\Data\master – tables.csv file.

Now let’s take a look at the data flow associated with the Data Flow task (shown in Figure 2). The data flow includes two components:

  • OLE DB source: Queries the sys.tables catalog view in the current database (in the Database variable) to retrieve the name, created date, and modified date for each table in the current database.
  • Flat File destination: Inserts the table-related data into a .csv file specific to the current database.


Figure 2: Data Flow in the PropertyExpressions.dtsx SSIS package


Figure 3: User-Defined Variables in the
PropertyExpressions.dtsx SSIS package

To support the various tasks in the PropertyExpressions.dtsx package, I created three variables (shown in Figure 3). Notice that I’ve defined the Databases variable as an object, the Database variable as a string, and the DatabaseID variable as an integer.

As you can see, PropertyExpressions.dtsx is a very basic SSIS package, but it contains all the components necessary to demonstrate how to use property expressions. Now let’s take a look at how that is done.

Note: I created the PropertyExpressions.dtsx package in SSIS 2005. However, I upgraded the package to SSIS 2008 to ensure that the property expressions features are implemented the same in both versions. I noticed no difference between the two.

Note also that this article assumes that you’re familiar with how to create an SSIS package. For more information about any of the specific components used in the PropertyExpressions.dtsx package, refer to SQL Server Books Online. In addition you can download the SSIS 2005 version of the PropertyExpressions.dtsx package here

Creating Property Expressions

The process of creating a property expression is very straightforward. You can add an expression to any read/write property in most of a package’s objects, which includes the package itself, the containers and tasks in the control flow, connection managers, event handlers, and log providers. As you’ll see later in the article, you can also add expressions to some of the properties in data flow components, but this functionality is much more limited than the control flow.

Accessing a Component’s Properties


Figure 4: SqlSrv2005 connection manager properties

The first step in adding a property expression is to access a component’s properties. Each component supports a set of properties that you can view in the Properties window in Business Intelligence Development Studio (BIDS). Figure 4 shows the properties for the SqlSev2005 connection manager in the PropertyExpressions.dtsx package.

If the Properties window is not displayed, right-click the component whose properties you want to view, and then click Properties. If the Properties window is already open, simply select the component and the properties will be displayed in the window.

Notice in Figure 4 that the SqlSrv2005 connection manager’s properties include the Expressions property. By default, no expressions are defined on a component’s properties. If a property expression has been defined, you can view it by clicking the plus (+) sign to the left of the Expression property. Any expressions that have been defined are then displayed immediately beneath the Expressions listing.

In this case, I have defined an expression on the InitialCatalog property, which determines which database to connect to when accessing the specified instance of SQL Server. I’ll go into more detail about the expression itself later in the article.

Building your Property Expressions

To add an expression to a property on a specific component, click the ellipses button to the right of the Expressions property in the Properties window. This launches the Property Expressions Editor (shown in Figure 5). The Property Expression Editor lists any property expressions that have been defined on the selected component and lets you modify those expressions as well as add or delete expressions.


Figure 5: Property Expressions Editor in SSIS

The Property column in the Property Expressions Editor lists properties for which property expressions have been defined. For example, Figure 5 lists the InitialCatalog property, which has been defined with an expression. When you add a new property expression, you must first select a property from the drop-down list in the Property column (in the first available blank row). The list includes only those properties on which properties can be define and have not yet been defined.

Note: A read-write property on any given component can be associated with only one expression. In other words, you cannot define two separate expressions on the same property in the same component.

After you select the property from the Property drop-down list, you can then define an expression (or edit an existing one) directly in the Expression column.

If you need help in building an expression, click the ellipses button to the right of the Expression column. This launches Expression Builder (shown in Figure 6).


Figure 6: Expression Builder dialog box in SSIS

The Expression Builder dialog box provides several tools to help you create your expressions:

  • The upper-left window lists the system and user-defined variables that can be used in your expressions.
  • The upper-right window lists the functions and operators that you can include in your expressions.
  • The Expression window provides a workspace for creating your expressions. You can drag variables, functions, and operators from the two upper windows into the workspace.
  • The Evaluate Expression button launches the logic necessary to evaluate the expression in the Expression window. If the expression can be evaluated, the results of that evaluation are displayed in the Evaluated value window. If the expression cannot be evaluated, a message window appears and displays details about the expression. Sometimes the message is useful in determining why your expression could not be evaluated. Often, however, it is not.

The expression shown in Figure 6 is the one I created on the InitialCatalog property of the SqlSrv2005 connection manager. To create the expression, I simply dragged the Database user-defined variable to the Expression window. Expression Builder automatically added the necessary at (@) symbol, brackets, and User namespace, which are required when referencing a variable within a property expression.

That was all I needed to do to create the expression. Because I used the Database variable and the variable is populated with a new database name each time the Foreach Loop container runs, the SqlSrv2005 connection manager will connect to the current database whenever it is called from within the container.

I also created the following property expression on the ConnectionString property of the DatabaseTables connection manager:

The expression uses the Database variable in order to include the database name in the file name. To do this, I concatenated two strings with the variable. Note that the string values must be enclosed in double quotes. For the backslashes in the path name, I had to escape each one with an additional backslash. As a result, each time the data flow runs (for each database) a file is created for that database. For example, the path name and file name for the file created for the AdventureWorks database will be C:\Data\AdventureWorks - tables.csv.

Creating Property Expressions on Data Flow Components

The process of creating a property expression is the same for any type of property that supports an expression. In addition, the properties for most objects remain consistent for each instance of that object. For example, no matter how many instances of an Execute SQL task you add to the control flow, the properties will be the same.

This is not the case for the Data Flow task. Because you can add components to the data flow, the properties available to the task can vary. However, not all of those properties-few as a matter of fact-support property expressions. For specifics about which data flow component properties support expressions, see the topic “Data Flow Properties that Can Be Set by Using Expressions” in SQL Server Books Online.

To configure expressions on a property in a data flow component, you take the same steps as you would any other property in the Data Flow task. If a component has been added to the data flow and the component includes properties that can be configured with expressions, those properties are displayed along with the base properties in the Property drop-down list in the Property Expression Editor (see Figure 5).

For example, the data flow in the PropertyExpressions.dtsx package includes a Flat File Destination component that inserts data into a .csv file created for each database. The component includes the Header property, which can be configured with an expression. As a result, that property is included in the list of available properties in the Property Expression Editor, when viewed for the Data Flow task. This means you can define on an expression on that property.

For instance, suppose you want to include a note at the beginning of each .csv file that identifies the name of the database. You can define an expression similar to the following:

In this case, I simply concatenate two strings with the current value in the Database variable. As each .csv file is created, the note is added to the file.

Using Enumerated Constants in Property Expressions

Some properties in SSIS objects require value from an enumerator member list. This means that, although the property value is displayed in BIDS with a friendly name (the enumerated constant), the value is actually stored as a numeral. For example, an SSIS package supports the CheckpointUsage property. In BIDS, the property value will be displayed as Never, IfExists, and Always. However, the values are actually stored as 0, 1, and 2, respectively.

When you define an expression on a property that uses enumerated constants, you must use the numeric value in your expression rather than the friendly name. The challenge to this, of course, is that most people don’t have these numbers memorized. For some properties that use enumerated constants, such as the ResultSetType property in the Execute SQL task, you can find the numeric values in the topic “Using Enumerated Constants in Property Expressions” in SQL Server Books Online. For properties not included in this topic, you have to dig a little deeper. For example, I had to refer to Microsoft TechNet to find the numeric values for the IsolationLevel property of the Data Flow task ( DtsContainer.IsolationLevel Property ).

After you’ve identified the correct numeric value for a specific enumerated constant, you can use that value in your expressions. For example, I created the following expression on the IsolationLevel property of the Data Flow task in the PropertyExpressions.dtsx package:

In this expression, I compare the value of the DatabaseID variable to the number 5. The question mark-colon (? 🙂 symbols serve as a condition operator that returns one of two expressions based on the evaluation of a Boolean expression. The Boolean expression in this case is @[User::DatabaseID] < 5. If the DatabaseID value is less than 5, the expression evaluates to true, otherwise it evaluates to false. If true, the IsolationLevel property is set to 256 (ReadUncommitted), otherwise the property is set to 65536 (RepeatableRead).

Note: I use the DatabaseID value to determine which databases are system databases. By default, the first four databases created in SQL Server 2005 and 2008 are master, model, msdb, and tempdb. This lets me set the transaction level differently for the primary system databases compared to other databases.

Moving Ahead with Property Expressions

The example property expressions shown up to this point have been fairly basic, yet the concepts demonstrated in those expressions should represent many of the elements you’re likely to use in your expressions. However, you can create more advanced expression by using the various types of variables, operators, and functions available to SSIS expressions. For more details about SSIS expressions, see the topic “Integration Services Expression Concepts” in SQL Server Books Online. There you will be directed to additional information and examples. In the meantime, you should now have the information you need to get started adding property expressions to any of your SSIS packages.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter


This post has been viewed 73330 times – thanks for reading.

Tags: , ,

  • Rate
    [Total: 71    Average: 4/5]
  • Share

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novels 'Last Stand' and 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

View all articles by Robert Sheldon

  • Anonymous

    Looks great
    BUt the link to the download is dead

  • Andrew Clarke

    Re: Dead Link
    Should be OK now. There is also a link to the file in the speech-bubble.

  • Sergio Adriano Blum

    Great! nice tip… wasn’t aware of that!

  • Anonymous

    Been looking for a better way!

  • wang88nz

    Great article
    Thanks for the code source.

  • IreneKF

    Thank you
    This article is just what I was looking for! Would you please explain how the Execute SQL Task in the Foreach Loop Container retrieves the current value from the variable? How do I reference the CURRENT value?