Hadi Fadlallah
This image shows a screenshot of the SSIS expression builder

SSIS Expression Tasks vs Evaluating variables as expressions

August 27, 2019 by

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:

In SSIS, Expressions can be used within different tasks or components:

  1. SSIS Expression Task: creates and evaluates expressions that set variable values at runtime
  2. Variables: can be evaluated as an expression
  3. Task properties: several properties can be set as expression so they can change at runtime More information at Use Property Expressions in Packages
  4. 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
  5. Precedence constraints: conditions can be set using expressions rather than execution result (success, failure …)
  6. Derived Column Transformation: uses values created by using expressions either to populate new columns in a data flow
  7. Conditional Split: conditions are written as expressions in SSIS
  8. 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:

  1. 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)
  2. Functions and operators tree: A tree that contains all functions and operators provided by SSIS expression language. These functions are categorized as the following:
    1. Mathematical Functions: such as absolute (ABS), square root (SQRT) …
    2. String Functions: used to manipulate string such as SUBSTRING and REPLACE functions
    3. Date/Time Functions: used to manipulate date/time values such as DATEPART function
    4. NULL Functions: used for NULL handling
    5. Type Casts
    6. Operators: such as conditional (? :), comparison (==) …
  3. Expression editor: The textbox where the user must enter the expression
  4. Evaluated Value: The textbox where the expression evaluated value appears when the user clicks on the “Evaluate Expression” button

This image shows a screenshot of the SSIS expression builder

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.

this image shows a screenshot of the SSIS expression Task

Figure 2 – SSIS Expression Task

The Expression Task consists of the expression builder form. An expression must have the following form:

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.

This image shows a screenshot of the variable tab in Visual Studio and how to add an SSIS expression to a variable

Figure 3 – Setting variable expression

This image shows a screenshot of the expression properties of a variable

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:

This image shows how the variable icon changes after adding an expression

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:

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

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
Execute SQL Task in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
SSIS Flat Files vs Raw Files
SSIS Foreach Loop vs For Loop Container
SSIS: Execute T-SQL Statement Task vs Execute SQL Task
SSIS Lookup transformation vs. Fuzzy Lookup transformation
SSIS Pivot transformations vs. Unpivot transformations
SSIS Merge Join vs. Merge Transformation
Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View
SSIS XML Source vs XML task
SSIS Script task vs. Script Component
SSIS term extraction vs. term lookup
Hadi Fadlallah
ETL, Integration Services (SSIS), SSIS monitoring

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. 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

168 Views