Loop 5 records at a time and assign it to a variable

Question

I have a table of 811 entries. I want to get five records at a time and assign it to a variable. The next time I run the foreach task in SSIS , it will run five more entries and overwrite the variable. I tried to do with the cursor, but could not find a solution. Any help would be greatly appreciated. I have a table like this, for example,

 ServerId ServerName 1 Abc11 2 Cde22 3 Fgh33 4 Ijk44 5 Lmn55 6 Opq66 7 Rst77 . . . . . . 

I want the request to take the first five names as follows and assign it to a variable

 ServerId ServerName 1 Abc11 2 Cde22 3 Fgh33 4 Ijk44 5 Lmn55 

Then the next loop takes five more names and overwrites the value of the variable, etc. until the last record is used.

Answer 1

Given ltn's answer, you can limit the lines in SSIS.

The design will look like

Step 1: Create Variables

  Name DataType Count int Initial int Final int 

Step 2: for the first SQL execution task, write sql to store the counter

  Select count(*) from YourTable 

On the General tab of this task, select ResultSet as Single Row .

On the ResultSet tab, display the result in a variable

  ResultName VariableName 0 User::Count 

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

Step 4: Inside the for loop, drag the SQL Task and write the expression

In the parameter display, map the variable initial

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

Result Tab

  Result Name Variable Name 0 User::Final 

Inside DFT, you can write sql to get specific rows

Click "Options" and select the variable initial and FINAL

Answer 2

if your data will not be updated between paging cycles and the sort order is always the same, then you can try an approach similar 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