ssis 表达式任务
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.
在本文中，我将首先介绍SSIS表达式，然后简要描述“表达式任务”以及如何将变量作为表达式求值。 然后，我将对这两个功能进行比较，以说明它们之间的异同。
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.
本文是SSIS功能面对面系列文章中的第二篇，旨在消除任何混淆并说明SQL Server Integration Services提供的相似功能之间的某些区别。
You can check my previous article in this series: SSIS OLE DB Source: SQL Command Vs Table or View
您可以查看本系列的上一篇文章： SSIS OLE DB源：SQL命令与表或视图
SSIS表达式概述 (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:
通常，SSIS中的表达式是文字，函数和运算符的组合，产生单个数据值。 表达式可以由单个值（“ abc”）或变量（@ [User :: FilePath]）或函数（GETDATE（））组成，也可以更复杂并且包含一些条件（CASE语句）或一些数学运算或一些字符串操作，例如：
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中，可以在不同的任务或组件中使用表达式：
- SSIS Expression Task: creates and evaluates expressions that set variable values at runtime SSIS表达式任务：创建并评估在运行时设置变量值的表达式
- Variables: can be evaluated as an expression 变量：可以作为表达式求值
- Task properties: several properties can be set as expression so they can change at runtime 任务属性：可以将多个属性设置为表达式，以便它们可以在运行时更改。 Use Property Expressions in Packages在包中使用属性表达式的更多信息
- Components properties: few properties can be set as SSIS expression so they can change at runtime. 组件属性：几乎没有属性可以设置为SSIS表达式，因此它们可以在运行时更改。 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 条件拆分：条件在SSIS中写为表达式
- Containers: For each loop, and for loop container properties can be evaluated as expressions, for example, the file enumerator directory 容器：对于每个循环，对于for循环，容器属性都可以作为表达式求值，例如，文件枚举器目录
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
要了解有关SSIS中这些表达式的更多信息，您可以参考官方文档，因为它包含非常有用的信息： Integration Services（SSIS）表达式
表情生成器 (Expression Builder)
The SSIS Expression builder is the form used to build the SSIS expression, it is composed of 4 parts:
“ SSIS表达式”构建器是用于构建SSIS表达式的形式，它由4部分组成：
- 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 (==) …
- 函数和运算符树：包含SSIS表达式语言提供的所有函数和运算符的树 。 这些功能分为以下几种：
- 数学函数：例如绝对（ABS），平方根（SQRT）…
- 字符串函数：用于操作字符串，例如SUBSTRING和REPLACE函数
- 日期/时间函数：用于操作日期/时间值，例如DATEPART函数
- NULL函数：用于NULL处理
- 类型转换
- 运算符：例如条件（？:)，比较（==）…
- 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
图1 – SSIS Expression Builder表单
表达任务 (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.
此功能是SQL Server 2012中新增的功能，它使用户可以在运行时设置变量值，而无需执行脚本任务，并且具有Visual Basic或C＃编程语言的任何知识。 一个表达式任务只能用于设置一个变量值，当我们需要设置多个变量值时，必须为每个变量添加一个表达式任务或使用脚本任务。
Figure 2 – SSIS Expression Task
图2 – SSIS表达任务
The Expression Task consists of the expression builder form. An expression must have the following form:
表达式任务由表达式生成器表单组成。 表达式必须具有以下形式：
@[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 …) 映射任务输出到变量（对每个循环容器执行SQL任务…）
- 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.
为了将变量作为表达式求值，我们必须从变量选项卡中选择变量，并将EvaluateAsExpression属性更改为True ，并且必须在expression属性内添加一个表达式。
Figure 3 – Setting variable expression
图3 –设置变量表达式
Figure 4 – Variable properties
图4 –变量属性
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.
请注意，在较早的SSIS版本中，必须手动设置EvaluateAsExpression属性，但是在较新的Visual Studio版本中，当向该属性添加SSIS表达式时，该属性将自动设置为True。
In addition, after setting the expression a function (fx) icon will appear beside of the variable name:
此外，设置表达式后，在变量名称旁边将显示一个函数（fx）图标：
Figure 5 – fx icon beside of variable name
图5 –变量名称旁边的fx图标
SSIS表达任务与变量表达 (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.
很多次有人问我，为什么在SQL Server 2012中添加了此表达式任务， 为什么在我们可以将其评估为表达式的同时使用此任务设置变量值 。 我完全同意使用这两种方法可以完成许多任务，但是在本节中，我将展示一些必须使用Expression Task的用例，以及一些不能使用Expression Task的用例。
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 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 a SSIS expression Task after the step we need will solve this issue.
当您必须在特定任务之后或在特定条件下更改变量值时，需要“表达式任务”。 例如，假设您有两个变量@ [User :: TestVariable]和@ [User :: ExpressionVariable] ，其中@ [User :: ExpressionVariable]被视为表达式，并且在控制流任务之间动态变化。 如果我们需要在执行特定的Task后捕获变量值并将其存储在另一个变量中，则无法使用变量表达式来实现这一点，因为如果我们向@ [User :: TestVariable]添加一个简单的表达式，例如@ [User :: ExpressionVariable ]变量，则最后一个变量将保持其值不变，并且将在添加SSIS表达式时充当第一个变量的副本，我们需要执行的步骤后的任务将解决此问题。
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:
另外，假设您需要为每个循环容器在每次迭代之后增加一个变量值。 使用带有类似表达式的Task表达式，这将非常容易：
@[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.
虽然使用变量表达式无法实现。 有关更多信息，您可以参考SSIS中的显示foreach循环迭代编号 。
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 expression and it will be evaluated after each task automatically.
另一方面，在某些情况下，我们需要在所有控制流程步骤中动态评估变量。 在类似的情况下，我们需要在每个任务之后添加一个SSIS表达式任务，或者我们可以简单地将变量作为表达式求值，并且它将在每个任务之后自动求值。
有用的网址 (Helpful Links)
There are many helpful links that you can refer to in order to learn more about SSIS expressions:
您可以参考许多有用的链接，以了解有关SSIS表达式的更多信息：
- SSIS Script component vs derived column SSIS脚本组件与派生列
- SSIS Conditional Split Transformation overview SSIS条件拆分转换概述
目录 (Table of contents)
翻译自: https://www.sqlshack.com/ssis-expression-task-vs-evaluating-variable-as-expression/
ssis 表达式任务
1440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
