How to: Use an Expression in a Data Flow Component
This procedure describes how to add an expression to the Conditional Split transformation or to the Derived Column transformation. The Conditional Split transformation uses expressions to define the conditions that direct data rows to a transformation output, and the Derived Column transformation uses expressions to define values assigned to columns.
To implement an expression in a transformation, the package must already include at least one Data Flow task and a source. For information about adding items to packages, see the following topics:
- How to: Add a Task or a Container to a Control Flow
- How to: Add a Component to a Data Flow
- How to: Connect Components in a Data Flow
To create an expression
In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
In SSIS Designer, click the Control Flow tab, and then click the Data Flow task that contains the data flow in which you want to implement an expression.
Click the Data Flow tab, and drag either a Conditional Split or Derived Column transformation from the Toolbox to the design surface.
Drag the green connector from the source or a transformation to the Conditional Split or Derived Column transformation.
Double-click the transformation to open its dialog box.
In the left pane, expand Variables to display system and user-defined variables, and expand Columns to display the transformation input columns.
In the right pane, expand Mathematical Functions, String Functions, Date/Time Functions, NULL Functions, Type Casts, and Operators to access the functions, the casts, and the operators that the expression grammar provides.
Depending on the transformation, do one of the following to build an expression:
In the Conditional Split Editor dialog box, drag variables, columns, functions, operators, and casts to the Condition column. Alternatively, you can type an expression directly in the Condition column.
In the Derived Column Editor dialog box, drag variables, columns, functions, operators, and casts to the Expression column. Alternatively, you can type an expression directly in the Expression column.
Note
When you remove the focus from the Condition column or the Expression column, the expression text may change to a red color indicating that the expression syntax is incorrect.
Click OK to exit the dialog box.
Note
If the expression is not valid, an alert appears describing the syntax errors in the expression.
See Also
Concepts
Conditional Split Transformation
Derived Column Transformation
Data Flow Task
Creating Package Data Flow
Other Resources
Integration Services Expression Reference
Integration Services Sources
Expression How-to Topics (SSIS)