Expression - The secret of Dynamic SSIS

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: To split the source resultset into multiple resultset on the basis of expression value.
• The Derived Column transformation: Ne wcolumns can be derived that would have value of expression result. For example, Column1 + " ABC".

• Property expressions: To set the property of any object. For example, The following property expression can be used to set the Executable property of an Execute Process task. The expression uses a combination of string literals, operators, and functions. The expression uses the DATEPART and GETDATE functions and the conditional operator.

DATEPART("weekday", GETDATE()) ==2?"notepad.exe":"mspaint.exe"

If it is the second day in the week, the Execute Process task runs notepad.exe, otherwise the task runs mspaint.exe.

• Variables: Value of variable can be set to an expression. This way value of variable changes dynamically at run time. For example, GETDATE().
• Precedence constraints: The flow of control changed dynamically by setting an Expressions in precedence constraint. This works like IF clause to decide further process at run time. Expression 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.

In SSIS variables are associated with Namespace. By default, custom variables belong to the User namespace, and system variables belong to the System namespace. Resolution operator, consisting of two colons (::), is used for qualifying a variable with its namespace. For example, the following expression uses two variables named Count; one belongs to the User namespace and one to the MyNamespace namespace. Such as @[User::Count] > @[MyNamespace::Count]

 

An example of complex expression:

MakeFlag ==  TRUE && FinishedGoodsFlag == TRUE  && Size != (DT_WSTR,10)@BikeSize

 

Integration Services provides a set of tools for building and managing property expressions.

• The Expressions page, found in the custom editors for tasks, the For Loop container, and the Foreach containers. The Expressions page lets you edit expressions and view a list of the property expressions that a task, Foreach Loop, or For Loop uses.

• The Properties window, for editing expressions and viewing a list of the property expressions that a package or package objects use.

• The Property Expressions Editor dialog box, for creating, updating, and deleting property expressions.

• The Expression Builder dialog box, for building an expression using graphical tools. This tool is available in the Conditional Split Transformation Editor, Derived Column Transformation Editor. The Expression Builder dialog box can evaluate expressions for your review without assigning the evaluation result to the property.