SQL Server - SSIS Package to Export SQL Server to Multiple Flat Files (based on size of row count)

Asked By JPo

18-Nov-09 06:49 PM

I have the need to create an export job that will query a large amount
of data from a SQL Server database and produce a flat file.  Because
of the amount of data, I would like to create a maximum file size or
record count for each flat file - for example a query with 5M records
would produce 5 flat files, each with 1M records.

Can this be done within an SSIS package?  I only have experience with
the simple import/export wizard.

Thank you in advance for any help!

18-Nov-09 03:41 PM

I used to tell my COnsulting Clients:

If you are new to SSIS, I would not recommend attempting this in its
entirety. Start with small steps, maybe generting a single flat file from the
data.

Once you have that, experiment with how you would break up the source data
into equal buckets using WHERE clauses. Same those 'boundry' values in a SQL
table. Then set up a For Each that loops over the values and populates
variables. Inside the loop, have a parameterized SQL statement as the Source
in your data flow.

You might find some useful stuff at SQLIS.com or my blog:
http://toddchitt.wordpress.com/

Good Luck
=====
Todd C

24-Nov-09 12:42 PM

Yes, this can be done, and John Welch has two great blog posts on this
topic:

Part 1 (requires scripting) -
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx
Part 2 (does not require scripting) -
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-outputs-part-2.aspx

Phil
ssistalk.com