Expression Tasks allow the user to create expressions, using functions, literals, operators, as well as variables, and evaluate them. Depending on its complexity, an expression can be as simple as a concatination of two values or it can go up to be composed of string manipulation, and/or even some conditional or mathematical operations.
The Expresion builder is the workbench.
It is split into four main areas:
- The Variables and Parameters Tree;
- This box contains both User defined variables as well as System defined variables, such as MachineName, LocaleID, CreatorName, CreationDate, and more;
- The Functions, Casts, and Operators Tree.
This box contains:
- Mathematical Functions, such as Round, Square, Square Root, Absolute, and more;
- String Functions, such as FindString, Replace, Replicate, Left/Right Trim and more;
- Date and Time Functions, such as Get Date, Date Add, Date Difference, and more.
- Null Functions, such as IsNull, ReplaceNull, and other functions useful when handling Nulls;
- Type Casts, which offers functions that will help convert data;
- Operators, both mathematical and logical, as well as Bitwise;
- The Expression Editor, where the user constructs his/her expression;
- The Evaluated Value, it is where the expression value is displayed, when the user clicks on the “Evaluate Expression” button.
In order to use a variable, a functions, or an operator from the tree boxes, you will need to drag and drop them into the Expression editor. Double clicking on any of them will do nothing.
When you need to use a variable within a function, simply drag and drop the variable over the placeholder value of the formula, it will autoselect itself and replace it with the variable once you let go of the mouse button.
Expressions can be used in different tasks and/or components.
- You can simply drag and drop an Expression Task out of SSIS Toolbox. Double clicking on it, will open up the Expression Builder.
- You have a Variable evaluated as an Expression.
To do that, simply
- Open the Variable window;
- Create a new variable;
- Notice the Expression attribute on the left side;
- Click on the three dots to open the Expression builder;
- Build your expression and click OK;
- Notice that the EvaluateAsExpression property in the variable’s property panel has been changed to True, and the icon of the variable has also changed, it now has an “fx” symbol in the bottom corner.
Components properties can also be set as Expressions, if you need them to change at runtime.
- Expressions can be set using executions in Precedence constraints, instead of execution result.
- Conditional Split, as well as Containers. The for loop container properties can be evaluated as expressions.
Important Note: In older versions of SSIS, the EvaluateAsExpression property needs to be switched manually, while in the newest versions, this is done automatically once a validated expression is set.