条件执行SSIS中的SQL任务

I have an Execute SQL task in my SSIS package that should execute a SQL stored procedure only if a certain condition is true.

在我的SSIS包中有一个执行SQL任务，只有在某个条件为真时才应该执行SQL存储过程。

I have a user variable [User::run] that gets a value when the package is started. In my SQL task, I have the following code:

我有一个用户变量[user::run]，它在包启动时获取一个值。在我的SQL任务中，我有以下代码:

declare @run varchar(1)
set @run = '" + @[User::run] + "'
if @run = 'Y'
begin
EXEC sp_procedure()
end

However, the stored procedure never gets executed. Any help or suggestions would greatly be appreciated.

但是，存储过程永远不会被执行。如有任何帮助或建议，我们将不胜感激。

3 个解决方案

#1

This look like an expression not a sql.

这看起来像一个表达式，而不是sql。

Open Execute SQL Task , go to Expressions tab , add an expression to SqlStatementSource

打开执行SQL任务，转到Expressions选项卡，向SqlStatementSource添加一个表达式

"declare @run varchar(1)
set @run = '" + @[User::run] + "'
if @run = 'Y'
begin
EXEC sp_procedure()
end"

"you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task." Read more

“您可以使用表达式将参数值连接到查询字符串中，并设置任务的SqlStatementSource属性。”阅读更多

#2

Just set

设

@run = @[User::run]

Do not add double quotes.

不要添加双引号。

#3

What if instead of dynamically setting the [User::run] variable, you manually set it. Does it execute the procedure then? If yes, you know there's an issue with setting the variable, if no, then you know it's something with the task.

如果不是动态设置[User::run]变量，而是手动设置。那么它执行这个过程吗?如果是，你知道设置变量是有问题的，如果不是，那么你就知道它与任务有关。

Is there a particular reason you want to use an if statement to try and execute the procedure? I'd recommend two precedence constraints. One directs to the end of the job, perhaps an email stating that it finished and log any results. The other directs to the stored procedure.

是否有特定的原因需要使用if语句来尝试和执行过程?我推荐两个优先约束。一个是指向工作的结尾，可能是一封表明它已经完成并记录任何结果的邮件。另一个指向存储过程。

As an example, one precedence constraint would connect from your first step to the second step, second step being your exec t-sql. The connector properties would be as seen below to evaluate if variable is set correctly, then it would flow to your T-SQL task.

例如，一个优先约束将从第一步连接到第二个步骤，第二个步骤是您的exec t-sql。连接器属性将如下所示，以评估变量是否设置正确，然后它将流向T-SQL任务。

Here is what the other connector would be set to as well, it would flow to another task, one that would end the ssis package.

这是另一个连接器的设置，它将流向另一个任务，一个将结束ssis包的任务。

#1