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. The key information to remember is that expressions always return a single value.

Expression Usage in SSIS

In SSIS, expressions can be used to implement a decision-based condition, create and update values in data columns, assign values to variables, define constraints in precedence constraints, and provide the expressions used by the For Loop container. This section describes the elements in SSIS that can use expressions.

Conditional Split Transformation

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.

Derived Column Transformation

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

Variables can be referenced in expressions. A SSIS package supports the following two types of variables:

• System variables These variables contain package, task, and transformation metadata such as name, ID, start time, version, creation date, machine name, user name, and so on.

• User variables These variables are created by the package designer and can contain any desired data stored in a variety of data types.

Expressions reference variables using @[System::VariableName] or @[User::VariableName] syntax.

Precedence Constraints

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 @[User::A] > @[User::B] compares two user-defined variables to determine whether the constrained task runs.

The for Loop Container

The For Loop container repeats a set of tasks until an expression evaluates to false. The For Loop container uses the following three expressions:

• The first expression initializes the variable.

• The second expression determines whether the set of tasks should be repeated.

• The third expression determines how the variable should be changed each time the set of tasks is repeated.

Expressions Elements

Expressions are based on an expression language, which is similar to C or C#. This language includes the following three elements:

• 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 Provides a list of available data columns and system and user variables and a list of functions and operators that is used to build expressions

More Info 

You can learn more about the language on which expressions are based in the SQL Server Books Online article, "Integration Services Expression Concepts" at the following URL: http://msdn2.microsoft.com/en-us/library/ms141827.aspx.

Building Expressions

The expression builder is a graphical tool for building expressions and is available in the dialog boxes for the Conditional Split and Derived Column transformations. To build expressions, you can drag and then drop items from either list onto the expression column. The expression builder automatically adds needed syntax elements, such as the @ prefix, on variable names.