sql-server - SSIS 包循环遍历存储过程名称表并执行它们

Question

我创建了一个表，将所有存储过程名称保存在一列中。我目前正在使用“执行 SQL 任务”将这些名称作为“结果集”提取到对象变量 strProcs 中。

我需要遍历所有这些存储过程名称并以相同的顺序执行它们。我如何使用 ForEach 循环容器或其他优雅的解决方案来实现这一目标？感谢您的帮助。

Answer 1

首先，创建一个字符串变量，比方说ProcName .这将在我们遍历循环时保存存储的过程名称。

接下来，创建一个 Foreach 循环，将 Enumerator 设置为 Foreach ADO Enumerator , 将源变量设置为 User::strProcs对象变量。然后下Variable Mappings在左 Pane 中，添加一个新条目，选择 User::ProcName并将索引设置为 0(假设过程名称是 strProcs 的第一列)。

然后，添加一个 Execute SQL Task在循环容器内。配置与数据库的连接，然后设置 SQLSourceType至 Direct input .对于 SQLStatement ，您将使用 ProcName 变量构建一个简单的动态查询。它看起来像这样:

DECLARE @ProcName VARCHAR(128) = ?
DECLARE @query VARCHAR(MAX) = 'EXEC ' + @ProcName

EXECUTE(@query)

点击 OK保存查询，然后转到 Parameter Mapping在左 Pane 中。添加一个条目，将变量名称设置为 User::ProcName , 方向Input , 数据类型为 VARCHAR ，参数名称为 0，参数大小为 128。点击 OK保存 Execute SQL Task配置。

就是这样!请注意，这是对不包含参数的过程的简化解释。如果您需要传递参数，则必须将它们添加到分配给 @query 变量的值中，以便它们与其余的动态 sql 一起执行。

编辑:

Ravi 问道:“我将按顺序运行查询(大约 200 个存储过程)并且表中确实有一个“ID 号”字段。我是否有可能更新开始时间，执行程序并将结束时间更新回数据库？”

我假设您的数据库中有一张表，其中包含 ID、过程名称、LastStartTime 和 LastEndTime。而且我还假设您在 SSIS 中设置的 ADO 记录具有 ID 值和过程名称，假设列也按此顺序排列。

使用上面创建的内容，您可以按照以下步骤修改一些任务并获得扩展的解决方案。

首先，创建一个 Int32 变量，我们称它为 ProcID .接下来，我们将修改 Foreach 循环配置，以便将 ID 加载到 ProcID 中。右键单击 Loop 容器并选择 Edit .点击 Variable Mappings在左 Pane 中添加 User::ProcID在 Variable 下并将 Index 设置为 0。设置 User::ProcName 的 Index到 1. 点击 OK将更改保存到循环。

接下来，您将修改 Execute SQL Task执行动态 SQL。所以打开那个配置编辑器，并更新 SQLStatement所以它看起来像这样:

DECLARE @ProcName VARCHAR(128) = ?
DECLARE @ProcID INT = ?
DECLARE @query VARCHAR(MAX) = 'EXEC ' + @ProcName

UPDATE ProcTable
SET LastStartTime = GETDATE()
WHERE ID = @ProcID

EXECUTE(@query)

UPDATE ProcTable
SET LastEndTime = GETDATE()
WHERE ID = @ProcID

您可以 UPDATE proc 调用之前的表，然后在 proc 调用之后再次。您在这里不需要脚本任务。

我们还将另一个变量映射到我们的 SQL 脚本中，因此在您编辑 SQLStatement 之后, 点击 Parameter Mapping在左 Pane 中，点击 Add按钮，然后选择 User::ProcID作为变量名，数据类型可以保持为LONG ，将参数名称设置为 1，将参数大小设置为 -1。点击确定。

然后您就完成了并准备好进行测试。