一次循环 5 条记录并将其分配给变量 [英] Loop 5 records at a time and assign it to variable
问题描述
我有一个包含 811 条记录的表格.我想一次获取五个记录并将其分配给变量.下次当我在 SSIS 中运行 foreach
循环任务时,它将循环另外 5 条记录并覆盖变量.我试过用游标做,但找不到解决方案.任何帮助将不胜感激.我有这样的桌子,例如
I have a table of 811 records. I want to get five records at a time and assign it to variable. Next time when I run the foreach
loop task in SSIS, it will loop another five records and overwrite the variable. I have tried doing with cursor but couldn't find the solution. Any help will be highly appreciated. I have table like this for e.g.
ServerId ServerName
1 Abc11
2 Cde22
3 Fgh33
4 Ijk44
5 Lmn55
6 Opq66
7 Rst77
. .
. .
. .
我希望查询应采用如下前五个名称并将其分配给变量
I want query should take first five names as follows and assign it to variable
ServerId ServerName
1 Abc11
2 Cde22
3 Fgh33
4 Ijk44
5 Lmn55
然后下一个循环取另外五个名称并覆盖变量值,依此类推,直到消耗完最后一条记录.
Then next loop takes another five name and overwrite the variable value and so on till the last record is consumed.
推荐答案
考虑到 ltn 的回答,您可以通过这种方式实现限制 SSIS 中的行数.
Taking ltn's answer into consideration this is how you can achieve limiting the rows in SSIS.
设计看起来像
第一步:创建变量
Name DataType
Count int
Initial int
Final int
第 2 步:对于第一个执行 SQL 任务,编写 sql 来存储计数
Step 2 : For the 1st Execute SQL Task write the sql to store the count
Select count(*) from YourTable
在此任务的 General
选项卡中,选择 ResultSet 作为 Single Row
.
In the General
tab of this task Select the ResultSet as Single Row
.
在ResultSet选项卡中将结果映射到变量
In the ResultSet tab map the result to the variable
ResultName VariableName
0 User::Count
第 3 步:在 For 循环容器中输入如下所示的表达式
Step 3 : In the For Loop container enter the expression as shown below
第 4 步:在 For 循环内拖动一个执行 SQL 任务并编写表达式
Step 4 : Inside the For Loop drag an Execute SQL Task and write the expression
在参数映射中映射initial
变量
VariableName Direction DataType ParameterName ParameterSize
User::Initial Input NUMERIC 0 -1
结果集标签
Result Name Variable Name
0 User::Final
在 DFT 中,您可以编写 sqL 以获取特定行
Inside the DFT u can write the sqL to get the particular rows
点击参数并选择变量INITIAL
和FINAL
这篇关于一次循环 5 条记录并将其分配给变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!