papalarge


I have a table that's 25,000,000 records... about 10 fields. I need to export this data to a flat file in no more than 500,000 record chunks. I've tried the following algorithm, adding a flag field called "exported" with default value 0.

do:
- mark random 500,000 records, setting exported = -1
- export everything in that table where exported = -1
- set exported = 1 where exported = -1
loop

This was pretty slow, taking about 10 hours last night to run.

I find myself wanting a sort of a split dataset task in SSIS, being able to split records a chunk of records out of a dataset and handle them. Anyone have ideas for me



Re: Best way to split a dataset into manageable chunks?


Re: Best way to split a dataset into manageable chunks?

jwelch

I like Phil's answer Smile, but Jamie has a good post about this too.

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

It's always good to have a few options.







Re: Best way to split a dataset into manageable chunks?

Rafael Salas



Excellent posts; I saw this question yesterday and was lthinking about an 'easy' way....but I don't think I can beat Jamie's one.