Expressions

An expression is a combination of elements that produces a single value. The elements that make up an expression can include variables, literal values, functions and procedures, and operators. An example expression would be 1<2, which produces the value true.

Note

While the expression language syntax is derived from the C family of languages, it is not case-sensitive.

Many items in SSIS support expressions. All tasks support expressions for properties, replacing the Dynamic Properties task from DTS. The For Loop and Foreach Loop containers use expressions to specify their looping conditions. See Chapter 4 for more information on these containers. Some tasks, such as the Derived Column task, use expressions to define their output.

Dynamic Properties

Integration Services provides a method of making your tasks dynamic by supporting expressions for task properties.

To view the property expressions for a task, right-click the task and select Edit. This will bring up the task editor window for that task. There will be a list on the left of the different property groupings you can edit. Each task will have an Expressions group that should be the last in the list, as seen in Figure 7-1.

Figure 7-1

The Expressions property editor will have a plus sign next to it that you can use to expand it to see all the expressions set for any properties. To create a new expression or edit an existing one, click in the Expressions text box and then click the ellipsis button. This will bring up the Property Expressions Editor window, as shown in Figure 7-2.

Figure 7-2

The Property Expressions Editor window has a Property column and an Expression column. The Property column provides a drop-down for selecting the task property you wish to create an expression for. Once you have selected the property, you can type the expression into the Expression column or you can click the ellipsis button to bring up the Expression Builder window as shown in Figure 7-3.

Figure 7-3

The Expression Builder window is simple but powerful. A label at the top lets you know the property whose expression you're editing. There is a tree view on the left of all the variables available to this task. On the right there is a tree view containing groups of all the available expression functions and operators. Selecting a function or operator provides a description of its purpose below the tree view. You can also drag a function to the Expression text box rather than typing it.

The Expression editing text box is located below the variable and function views. This is where you will enter your expression. Since you selected the DelayValidation property in Figure 7-2, you must enter an expression that evaluates to a Boolean value because DelayValidation is a Boolean property. For this example, try putting in 1<2. Once you are done, you can test the expression by clicking the Evaluate Expression button in the bottom left. The value will be displayed above this button. You should see the value True printed to the screen. If there is an error evaluating your expression, you will receive a message box explaining the error. Try entering an expression that returns a non-Boolean value such as GETDATE() and see what happens.

Expressions in Tasks

Expressions are also used in some containers and tasks to perform the work of the task. The Derived Column transformation works solely on expressions.

You will now see how expressions are used in the Derived Column transformation. Create a new SSIS package and add a Data Flow task. Open the Data Flow task and add an OLE DB Source. Use the AdventureWorks database and the [HumanResources].[vEmployee] view. Bring over only the FirstName, MiddleName, and LastName columns for this example.

Add a Derived Column task to the Data Flow and connect the OLE DB Source to it. Double-click the Derived Column task to bring up the Transformation Editor window. As you can see in Figure 7-4, this window is similar to the Expression Builder window. The big difference is that the Expression text box is gone. Now there is a grid that is used to define the derived columns.

Figure 7-4

In the first row of the grid, type FullName in the Derived Column Name field. Next, select <add as new column> in the Derived Column field.

Finally, in the Expression field, enter the following expression:

 FirstName + ""+ MiddleName + ""+ LastName 

This expression is very simple. It concatenates each element of the person's name with a space between each element.

Click OK to close the Expression Builder window. Add a Flat File or Excel Destination to the Data Flow and connect the Derived Column task to it. Once you've got your destination set up, run the package.

Your package should run fine with no errors. Open up your destination file to see a list of all the employees with a FullName column at the end of each record. If you used the Excel Destination, you should have a file similar to the one displayed in Figure 7-5.

Figure 7-5

You should quickly notice that not every record will have a value in the FullName column. You might also notice that for these records, the person has no value for MiddleName. This is probably not what you expected. Since these records have a NULL value for MiddleName, they caused the expression to evaluate improperly. You must modify the expression to account for NULL values.

Open the Derived Column task again and change the FullName expression to the following:

 FirstName + (ISNULL(MiddleName)? "": ""+ MiddleName + "") + LastName 

This expression uses the ISNULL() function along with the ternary operator to add the MiddleName only if its value is not NULL.

If you've developed only in Visual Basic or similar languages, you may not have heard of the ternary operator. The syntax of the expression language is derived from the C family of languages and inherits this operator from it. The ternary operator works much the same as the IIF() function in Visual Basic. The first argument is the Boolean condition to check — in this case, ISNULL(MiddleName). After the ? is the value returned if the condition is true, and after the colon is the value returned if the condition is false. In this example, a space is returned if MiddleName is NULL; otherwise, the value of MiddleName is returned with a spaced added to the front and back.

Note

While some expression language functions have the same name as T-SQL functions, their syntax is not always the same.

Save the changes, and make sure that you close the Excel file before running the task again. With the new expression, every row should have a value for the FullName column. Your output should look like that in Figure 7-6.

Figure 7-6

Notice that Robert Tamburello had an empty field in the first example, but his full name is now displayed properly.

The new expression language provided with SSIS is powerful and easy to use. For a full explanation of the expression language in SSIS, including documentation for all built-in functions, see SQL Server 2005 Books Online.