In this article, I will first give an introduction about SSIS expressions, then I will describe briefly the Expression Task and how to Evaluate a variable as expression. Then I will do a comparison between these two features to illustrate the similarities and differences between them.
This article is the second article in the SSIS feature face to face series which aims to remove any confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.
You can check my previous article in this series: SSIS OLE DB Source: SQL Command Vs Table or View
SSIS Expression overview
In general, expressions in SSIS is a combination of literals, functions, operators that yields a single data value. An expression can be composed of a single value (“abc”) or a variable (@[User::FilePath]) or a function (GETDATE()), or it can be more complex and contains some conditionals (CASE statement) or some mathematical operations or some string manipulation, as Example:
|
1 2 |
ISNULL(@[User::FilePath]) == False && @[User::FilePath] != “” ? NULL(DT_WSTR,50) : TOKEN(@[User::FilePath],”\\”,TOKENCOUNT(@[User::FilePath],”\\”)) |
In SSIS, Expressions can be used within different tasks or components:
- SSIS Expression Task: creates and evaluates expressions that set variable values at runtime
- Variables: can be evaluated as an expression
- Task properties: several properties can be set as expression so they can change at runtime More information at Use Property Expressions in Packages
- Components properties: few properties can be set as SSIS expression so they can change at runtime. More information at Use an Expression in a Data Flow Component
- Precedence constraints: conditions can be set using expressions rather than execution result (success, failure …)
- Derived Column Transformation: uses values created by using expressions either to populate new columns in a data flow
- Conditional Split: conditions are written as expressions in SSIS
- Containers: For each loop, and for loop container properties can be evaluated as expressions, for example, the file enumerator directory
To learn more about these expressions in SSIS, you can refer to the official documentation, since it contains very helpful information: Integration Services (SSIS) Expressions
Expression Builder
The SSIS Expression builder is the form used to build the SSIS expression, it is composed of 4 parts:
- Variables and parameters tree: A tree that contains all variables and parameters created in the package (when building an expression within a Data Flow Task it contains also the Pipeline columns)
- Functions and operators tree: A tree that contains all functions and operators provided by SSIS
expression language. These functions are categorized as the following:
- Mathematical Functions: such as absolute (ABS), square root (SQRT) …
- String Functions: used to manipulate string such as SUBSTRING and REPLACE functions
- Date/Time Functions: used to manipulate date/time values such as DATEPART function
- NULL Functions: used for NULL handling
- Type Casts
- Operators: such as conditional (? :), comparison (==) …
- Expression editor: The textbox where the user must enter the expression
- Evaluated Value: The textbox where the expression evaluated value appears when the user clicks on the “Evaluate Expression” button
Figure 1 – SSIS Expression Builder form
Expression Task
This feature was added in SQL Server 2012, it allows users to set a variable value at runtime without the need of a Script Task and to have any knowledge of Visual Basic or C# programming languages. An Expression Task can be used to set only one variable value, when we need to set multiple variable values we must add an Expression Task for each one or to use a Script Task.
Figure 2 – SSIS Expression Task
The Expression Task consists of the expression builder form. An expression must have the following form:
|
1 |
@[User::Variable] = Expression |
Evaluate Variable as an expression
A variable, in general, is a named object that stores a value. They are used to share values between different tasks and components or for configuration purposes. There are many ways to store values in variables:
- Setting value manually from the variables tab
- Using a Script Task
- Using a Script Component
- Using an Expression Task (as mentioned above)
- Mapping Tasks output to the variable (Execute SQL Task, for each loop container …)
- Evaluating value using an expression
In order to evaluate a variable as expression, we must select the variable from the variables tab, and change the EvaluateAsExpression property to True, and we must add an expression within the expression property.
Figure 3 – Setting variable expression
Figure 4 – Variable properties
Note that in older SSIS version you have to set the EvaluateAsExpression property manually, but in newer Visual Studio versions when you add an SSIS expression to this property is automatically set to True.
In addition, after setting the expression a function (fx) icon will appear beside of the variable name:
Figure 5 – fx icon beside of variable name
SSIS Expression Task Vs Variable Expression
Many times I was asked, why this Expression Task is added in SQL Server 2012, and why using this Task to set a variable value while we can Evaluate it as expression. I totally agree that there are many tasks that can be achieved using both approaches, but in this section, I will show some use cases where Expression Task is a must, and some other cases where Expression Task cannot be used.
The Expression Task is required when you have to change a variable value after a specific task or at a specific condition. As an example, assume that you have two variables @[User::TestVariable] and @[User::ExpressionVariable] where @[User::ExpressionVariable] is evaluated as an expression and it changes dynamically among the control flow tasks. If we need to catch the variable value after a specific Task execution and stores it within another variable we cannot use a variable expression to achieve that since if we add a simple expression like @[User::ExpressionVariable] to @[User::TestVariable] variable then the last one will keep changing its value and it will act as a replicate for the first variable while adding an SSIS expression Task after the step we need will solve this issue.
Also, assume that you need to increment a variable value after each iteration of a for each loop container. This would be very easy using an expression Task with a similar expression:
|
1 |
@[User::Variable1] = @[User::Variable1] + 1 |
While it cannot be achieved using a variable expression. For more information, you can refer to Display foreach loop iteration number in SSIS.
Another example is if we are iterating over files and each time we want to check if the file name contains a specific word, and we need to execute the next tasks based on this check result. In a similar case we cannot use a variable expression.
On the other hand, there are some scenarios where we need that the variable is evaluated dynamically among all the control flow steps. In a similar case, we need to add an SSIS Expression Task after each task or we can simply Evaluate the variable as an expression and it will be evaluated after each task automatically.
Helpful Links
There are many helpful links that you can refer to in order to learn more about SSIS expressions:
Table of contents
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023