Loop 5 records at a time and assign it to variable

问题:

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.

回答1:

Taking ltn's answer into consideration this is how you can achieve limiting the rows in SSIS.

The Design will look like

Step 1 : Create the variables

  Name       DataType 
  Count        int
  Initial      int
  Final        int

Step 2 : For the 1st Execute SQL Task write the sql to store the count

  Select count(*) from YourTable

In the General tab of this task Select the ResultSet as Single Row.

In the ResultSet tab map the result to the variable

  ResultName       VariableName
    0               User::Count

Step 3 : In the For Loop container enter the expression as shown below

Step 4 : Inside the For Loop drag an Execute SQL Task and write the expression

In Parameter Mapping map the initial variable

  VariableName    Direction   DataType   ParameterName   ParameterSize
   User::Initial   Input       NUMERIC     0                -1

Result Set tab

  Result Name     Variable Name
   0                 User::Final

Inside the DFT u can write the sqL to get the particular rows

Click on Parameters and select the variable INITIAL and FINAL

回答2:

if your data will not be update between paging cycles and the sort order is always the same then you could try an approach similiar to:

CREATE PROCEDURE TEST
(
    @StartNumber INT,
    @TakeNumber INT
)
AS
SELECT TOP(@TakeNumber) 
    *
FROM(
    SELECT
        RowNumber=ROW_NUMBER() OVER(ORDER BY IDField DESC),
        NameField
    FROM 
        TableName
)AS X   
WHERE RowNumber>=@StartNumber