SSIS: Extract SQL Data and Split across Flat files based on row count

All we need is an easy explanation of the problem, so here it is.

I have created a simple SSIS package that queries a table and extracts data to a flat CSV file. In production this extract could be millions of rows and I want to split the flat file destinations into multiple files based on row count.

So create new file each time we hit 100000 rows with filenames something like

  • SomeName_01_date.csv
  • SomeName_02_date.csv

I have found a paid for tool by ZappySys that will do this but cannot work out how to do it with just the normal SSIS toolbox, I may be missing something really simple.
I have found other posts and videos but some of them involve using additional code outside of the normal tool set, such as the techbrothersit website.

After reading up and from the comments this looks to be harder than expected.
If I change process to split the flat files based on a date column in the table would that be more straight forward?

Table has a short date column in this format 2020-07-30, each CSV file would contain just one days worth of extracted data (could be 100K+), that data is then deleted from the table. The deletion will occur after all data has been extracted.

I am trying to use a foreach/forloop container but struggling as this is totally new to me, any help would be appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Why not limit the data via the query that gets executed, either with the OFFSET and FETCH clauses or with a predicate you can slide the window on with each iteration in SSIS?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply