Home > SSIS Best Practices > SSIS – Expression Language

SSIS – Expression Language

I’ve has very little exposure to the expression syntax in SSIS so far. Expression language in SSIS is another powerful feature.

Here is the Integration Services Expression Reference on MSDN: http://msdn.microsoft.com/en-us/library/ms141232(SQL.90).aspx

Here is a nice blog on the expression language in SSIS: http://sqlblog.com/blogs/andy_leonard/archive/2009/01/30/an-introduction-to-the-ssis-expression-language.aspx

The syntax shares similarities with languages such as: C++, C#, Java, etc.

  • == for comparison
  • = for assignment

Here is what is said on MSDN:

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 SSIS 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.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: