在SSIS中显示foreach循环迭代次数 [英] Display foreach loop iteration number in SSIS
问题描述
我需要检查在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+中可用的任务
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 Expression Task
- Expression Task
您可以使用脚本任务来完成相同的过程,方法是创建计数器变量,在脚本任务中将其选择为ReadWrite变量,然后在脚本内部向主函数中添加类似的行:
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;
参考
- Increment a variable within a Foreach Loop and use it-SSIS
- Use SSIS Variables and Parameters in a Script Task
有多种显示数据的方式.一种是使用脚本任务.指定您的@[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循环内设置Task的Name
属性.在循环中的任务上单击鼠标右键,然后选择属性".在属性"部分中找到[+]Expressions
集合,然后单击右侧的省略号...
,然后在新窗口中,选择左侧的名称",并将右侧的表达式设置为
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)
这将两个字符串"My Task"连接起来,并将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
这篇关于在SSIS中显示foreach循环迭代次数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!