如何使用约束编辑器中查询的结果集有条件地执行SQL Task? [英] How to use the Result Set from a Query in the Constraint Editor to conditionally execute SQL Task?
问题描述
我的SQL脚本存储在@gempar = NULL和@beneficiary = '2018-01-01'中.
需要根据这些变量的内容执行下一个执行SQL任务.如果ISNULL(@gempar) = True，则我们不运行以下任务(因为@gempar 等于NULL ).另一方面，如果ISNULL(@beneficiary) = False，则我们运行以下任务(因为@beneficiary 不是NULL ).
My SQL Script is storing in @gempar = NULL and in @beneficiary = '2018-01-01'.
In need to Execute the next Execute SQL Tasks based on the content of those variables. IF ISNULL(@gempar) = True then we don't run the following task (because then @gempar is equal to NULL). On the other hand, IF ISNULL(@beneficiary) = False then we run the following task (because @beneficiary is not NULL).
下一个图像是执行SQL任务中的结果集:
The next image is the Result Set from the Execute SQL Task:
我在SSIS中创建了2个变量的受益人和gempar，但是我不确定它们是否应该在表达式列中按以下方式编写:@[User::gempar]和@[User::beneficiary]或它们现在位于下一张图像中:
I created the 2 variables beneficiary and gempar in SSIS but I am not sure if they should be written as following in the expression column: @[User::gempar] and @[User::beneficiary] or as they are now on the next image:
这是SSIS控制流:
优先约束编辑器的输出应为True.为什么它没有捕获@beneficiary中的值?
The Precedence Constraint Editor's Output should be True. Why is it not catching the value in @beneficiary ?
我希望 TRUNCATE GEMPAR TABLES 任务停止，并且 TRUNCATE BENE TABLES 任务继续.
I expect the TRUNCATE GEMPAR TABLES task to stop and the TRUNCATE BENE TABLES task to continue.
推荐答案
SSIS不允许SSIS变量为null.它的年龄稍大，并且上下文稍有不同，但这 article 进一步详细说明了SSIS变量是否为null.由于您使用的变量属于字符串数据类型，因此可以使用一个选项将变量与空白字符串进行比较.请记住，由于执行SQL任务中执行的操作，因此不不能保证这些变量为空.您可能需要在SELECT语句中替换空白字符串或其他值.例如，COALESCE(@beneficiary , '').
SSIS doesn't allow for SSIS variables to be null. It's somewhat older and in a slightly different context, but this article further details SSIS variables in regards to null. Since the variables you're using are of the string data type, an option compare the variable to a blank string. Keep in mind, that this does not guarantee these variables were null as a result of what was performed in the Execute SQL Task. You may need to substitute blank strings or another value in your SELECT statement for this. For example, COALESCE(@beneficiary , '').
在任务之间的优先约束"上单击鼠标右键，然后选择编辑".然后将评估运算更改为表达式并添加一个表达式，如下所示.根据您的预期结果，可能需要将评估操作"更改为表达和约束，并在值"字段中设置先前任务的状态.当@[User::beneficiary]变量为null时，下面的示例返回true，这将允许按照您所描述的那样执行以下任务.您可以将其反转，即对@[User::gempar]变量使用!=.
Right-click the Precedence Constraint between the tasks select Edit. Then change the Evaluation Operation to Expression and add an expression as follows. Depending on your expected outcome, the Evaluation Operation may need to be changed to Expression and Constraint, with the status of the prior task set in the Value field. The example below returns true when the @[User::beneficiary] variable is null, which will allow the following task to execute as you described. You can reverse this, i.e. use !=, for the @[User::gempar] variable.
@[User::beneficiary]) == ""
这篇关于如何使用约束编辑器中查询的结果集有条件地执行SQL Task?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
