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

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.

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

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.

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).

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.