在 SSIS 中显示 foreach 循环迭代次数

2021-12-30 00:00:00 etl sql-server foreach-loop-container ssis ssis-2017

我需要检查我在 Visual Studio 2017 中运行的 foreach 循环容器任务的迭代次数.我怎样才能做到这一点?

I need to keep a check on the iteration number for a foreach loop container task that I am running in Visual Studio 2017. How could I achieve this ?

推荐答案

(1) 使用表达式任务计算迭代次数

SSIS 2012+ 中可用的任务

在 Foreach 循环容器中，没有包含迭代次数的属性.您可以通过创建一个 Int 类型的 SSIS 变量来实现这一点，初始值等于 0.示例 @[User::Counter]

In Foreach Loop container, there is no properties that contains the iteration number. You can achieve this by creating a SSIS variable of type Int, with a initial value equal 0. example @[User::Counter]

在 Foreach 循环容器内，添加具有以下表达式的 Expression Task:

Inside the Foreach loop container, add an Expression Task with the following expression:

@[User::Counter] = @[User::Counter] + 1

有用的链接

  • SSIS 表达任务
  • 表达任务

您可以使用脚本任务实现相同的过程，通过创建计数器变量，在脚本任务中将其选择为读写变量，并在脚本内在主函数中添加类似的行:

You can achieve the same process using a Script Task, by creating the counter variable, select it as ReadWrite Variable in the Script Task, and inside the script add a similar line into the Main Function:

Dts.Variables["User::Counter"].Value = Convert.ToInt32(Dts.Variables["User::Counter"].Value) + 1;

参考资料

  • 在 Foreach 循环中递增变量并使用它-SSIS
  • 在脚本任务中使用 SSIS 变量和参数

有多种显示数据的方式.一种是使用脚本任务.指定您的 @[User::Counter] 变量在 ReadOnly 集合中，然后将该值发送到运行日志中

There are different ways of displaying data. One is to use a script task. Specify your @[User::Counter] variable is in the ReadOnly collection and then emit the value into the run log

    public void Main()
    {
        bool fireAgain = false;
        string message = "{0}::{1} : {2}";
        foreach (var item in Dts.Variables)
        {
            Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
        }

        Dts.TaskResult = (int)ScriptResults.Success;
    }

另一种方法是通过表达式设置 Foreach 循环中任务的 Name 属性.右键单击循环内的任务并选择属性.在 Properties 部分找到 [+]Expressions 集合，然后单击右侧的省略号 ...，然后在新窗口中，选择左侧的 Name 并设置右侧表达式为

A different approach would be to set the Name property of a Task within the Foreach Loop via an expression. Right-click on a Task within the Loop and select Properties. Find the [+]Expressions collection in the Properties section and click the right side ellipses ... and in the new window, select Name in the left hand side and set the right-hand side expression to be

"My Task " + RIGHT("000" + (DT_WSTR,3) @[User::Counter], 3)

这将连接两个字符串我的任务"并将 Counter 变量转换为字符串并用零填充它，因此它始终是一个 3 位数字

This concatenates two strings "My Task" and converts the Counter variable to a string and left pads it with zeroes so it's always a 3 digit number

相关文章