Integration Services Expression Reference
Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.
The following Integration Services elements can use expressions:
The Conditional Split transformation, which implements a decision structure, based on expressions, to direct data rows to different destinations. Expressions used in a Conditional Split transformation must evaluate to TRUE or FALSE. For example, Column1 > Column2.
The Derived Column transformation, which adds new columns to a data flow and populates the columns or updates existing columns with values created using expressions. For example, Column1 + " ABC".
Property expressions, which can use an expression to set property values. For example, "The processing of " + @TaskName +" is completed".
Variables, which can use an expression to set their value. For example, GETDATE().
Precedence constraints, which can use expressions to specify the conditions that determine whether the constrained task or container in a package runs. Expressions used in a precedence constraint must evaluate to TRUE or FALSE. For example, @A > @B.
For Loop container, which can use expressions to build the initialization, evaluation, and increment statements the looping structure uses. For example, @Counter = 1.
Expressions include three different elements: the expression grammar, the expression evaluator, and the expression builder. The expression grammar defines expression syntax and the operators, functions, and data types available for use in expressions.
The expression evaluator parses expressions and determines whether expressions adhere to the rules of the expression grammar.
The expression builder, available in the dialog boxes for the Conditional Split and Derived Column transformations, is a graphical tool for building expressions. The expression builder provides a list of available data columns, system and user variables and a list of functions and operators. To build expressions, you can drag and drop items from either list onto the expression column. The expression builder automatically adds needed syntax elements such as the @ prefix on variable names.
The following table lists the topics in this section.
Topic |
Description |
|---|---|
Describes expression evaluator syntax, the data types that the Data Transformation Pipeline uses, data type conversion, and expression elements. |
|
Describes the operators that the expression evaluator provides. |
|
Describes the functions that the expression evaluator provides. |
|
Provides expressions that use multiple operators and functions. |