Loop 10 records at a time and assign it to variable

Question

I have a table of 900 records. I want to get 10 records at a time and assign it to variable. Next time when I run the for each loop task in SSIS, it will loop another 10 records and overwrite the variable. Any help will be highly appreciated.

I have table like this for e.g

this is want I have tried execute sql task to pull 900 records to variable, connect Execute sql task to For each loop, inside for each loop have Data flow task, the source has sql query and destination is table.

select * from Dbo.JPKGD0__STP
where EMPID in ?

but this will pass each empid in 1 loop , so i wanted to pass 10 empids each time.

Please let me know if I need to use different approach/or other tasks to achieve this.

Try using NTILE() analytical function which divides your ordered data set into a bucket. eg: [Here](https://stackoverflow.com/questions/14355324/want-to-learn-more-on-ntile) — Naveen Kumar, Feb 05 '19 at 12:47

Hadi · Answer 1 · 2022-01-26T22:16:06.373

Step (1) - Create variables

You have to create two variables of type int:

@[User::RowCount] >> type int
@[User::Counter] >> type int
@[User::strQuery] >> type string

Assign the following expression to @[User::strQuery]:

"SELECT  EMPID
FROM     Dbo.JPKGD0__STP
ORDER BY EMPIDASC 
OFFSET  " + (DT_WSTR,50)@[User::Counter]  + " ROWS 
FETCH NEXT 10 ROWS ONLY "

Step (2) - Get Row Count

First, add an Execute SQL Task with the following command:

SELECT Count(*) FROM Dbo.JPKGD0__STP;

And store the result in @[User::RowCount] variable (check this link for more information).

Step (3) - For Loop Container

Now, Add a For Loop Container with the following expressions:

InitExpression: @[User::Counter] = 0
EvalExpression: @[User::Counter] < @[User::RowCount]
AssignExpression: @[User::Counter] = @[User::Counter] + 10

Inside the For loop container, add a Data flow task, with an OLE DB Source and a destination. In the OLE DB Source, select the Access Mode as SQL Command from variable and select @[User::strQuery] as a source.

Loop 10 records at a time and assign it to variable

1 Answers1

Step (1) - Create variables

Step (2) - Get Row Count

Step (3) - For Loop Container

References

Linked