Using Expressions in Packages
An expression is 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. In SQL Server 2005 Integration Services (SSIS), expressions can be used to define conditions for CASE statements, create and update values in data columns, assign values to variables, update or populate properties at run time, define constraints in precedence constraints, and provide the expressions used by the For Loop container.
The following elements in Integration Services can use expressions:
- The Conditional Split transformation 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, rows that meet the condition in the expression "Column1 > Column2" can be routed to a separate output.
- The Derived Column transformation uses values created by using expressions either to populate new columns in a data flow, or to update existing columns. For example, the expression Column1 + " ABC" can be used to update a value or to create a new value with the concatenated string.
- Variables use an expression to set their value. For example, GETDATE() sets the value of the variable to the current date.
- Precedence constraints 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, the expression @A > @B compares two user-defined variables to determine whether the constrained task runs.
- The For Loop container can use expressions to build the initialization, evaluation, and the incrementing statements that the looping structure uses. For example, the expression @Counter = 1 initializes the loop counter.
Expressions can also be used to update the values of properties of packages, containers such as the For Loop and Foreach Loop, tasks, connection managers, log providers, and Foreach enumerators. For example, using a property expression, the string "Localhost.AdventureWorks" can be assigned to the ConnectionName property of the Execute SQL task. For more information, see Using Property Expressions in Packages.
Expressions are based on an expression language, and the expression evaluator. The expression evaluator parses the expression and determines whether the expression follows the rules of the expression language. For more information, see Integration Services Expression Reference.
The expression builder, available in the Conditional Split Transformation Editor, Derived Column Transformation Editor dialog boxes, and in the Expression Builder dialog box, is a graphical tool for building expressions.
The expression builder provides folders that contain package-specific elements, and folders that contain the functions, type casts, and operators that the expression language provides. The package-specific elements include system variables and user-defined variables. In the Conditional Split Transformation Editor and Derived Column Transformation Editor dialog boxes, you can also view data columns. To build expressions for the transformations, you can drag items from the folders to the Condition or Expression column or you can type the expression directly in the column.
Note
The names of user-defined and system variables are case-sensitive.
Variables have scope, and the Variables folder in the expression builder lists only variables that are in scope and available to use. For more information, see Integration Services Variables.
The following procedure describes the steps to create an expression by using the expression builder.
To create an expression using the Expression Builder
See Also
Concepts
Other Resources
Creating Packages in SSIS Designer