Exportin XML files with limited record set with SSIS

Question

I have table for +2000 rows. Each row represent a instance in XML file I have managed to generate:

Output:

The need is to select 500 rows at a time, generate XML and save it as a file into selected folder. The naming of the file should be dynamic eg. file_name_20171128165412 and the next one file_name_20171128165520

Tried to solve with For each loop with variable parameters but not getting it right - also need ideas for saving and naming the XML files.

Answer 1

I don't use for loops too often but you can do this:

set the following variables:

loopcounter int set initial value = 0
rowsprocessed int set initial value = 500
sql string

1. Add a for loop container
2. Set the eval expression to @rowsprocessed <500 (This will make the last loop when processing less than 500 records
3. Set assign expression to @loopcounter = @loopcounter +1

4. Assign expression to sql to:

   ";with cte as ( select *,ROW_NUMBER() over (order by [the primary key]) rn from [your table] ) select * from cte where rn between " + cstr(@loopcounter*500+1) +" and "+cstr((@loopcounter+1)*500)

5. Use @sql as your extract query

6. Add a Row Count to dataflow and assign value to rowsprocessed.

Hope this helps.