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.

Edit:
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 stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply