Wednesday, March 07, 2012 6:18 AM
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.
- Edited by deeptideshpande Wednesday, March 07, 2012 6:19 AM
Wednesday, March 07, 2012 6:30 AM
You can use T-SQL Task to call a procedure that does pagingDECLARE @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,
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/
Wednesday, March 07, 2012 5:33 PMModerator
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