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

Solution 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

Solution 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