How can i loop through every 100 records of my SQL table with the help of For each loop container?

Question

Hi,

I would like to know how can i use For each loop container to loop through every 100 records of my table? I have a input file which has around 3000 records and i'm tranferring its contents to a SQL table. I need to generate an output XML file for every 100 records of that file. I'm aware of using for each loop container to loop through every record but i want to loop through every 100 records.

If there is any other way of achieving this functionality in SSIS then please let me know.

Thanks

---

Deepti

Answer 1

You can use T-SQL Task to call a procedure that does paging 

DECLARE @page_size INT;
DECLARe @page_nbr INT;


SET @page_size = 100;
SET @page_nbr = 1;


SELECT a, b, c, d
FROM (SELECT a, b, c, d,
                    ROW_NUMBER() OVER(
                    ORDER BY a DESC, b DESC, 
                                  c DESC, d DESC)
      FROM SomeTable) AS T(a, b, c, d, rn)
WHERE rn > (@page_nbr - 1) * @page_size 
   AND rn <= @page_nbr * @page_size;

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

You can also extract all the records and add a row number to them, then push the lot to a staging file in one DFT.  Follow that with a For Loop containing a DFT that reads the staging file and filters out groups of 100 based on the counter.

---

Talk to me now on