Suggested Design for Multi-File Export to Tab Delimited ...

[smedvid]

I have a request to split an existing text file output directly from a table into separate files in groups of 10k records. I already have a rowid (identity) in the table, now I am struggling with how to design a DTS to accomplish task. The table which presently feeds the existing single output text file; has ~60k rows. But, this can be dynamic and we may have 100K rows next month. The whole reason for this 10k per file is to accomodate another program which can appearently only run 10k records per modeling run. I plan to store the 10K max records per text file in a default lookup table; since it may change. Any design thoughts appreciated.

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[AlexCuse]

Hmmm, perhaps your best bet would be to write a looping package that's driven by your row identifier?

your source query could be something like:

select top 10000 *
from daTable
where rowid > ?
order by rowid asc

where the parameter is the last row id processed the last time through (you should be able to store it in a variable).

You could use a second variable to store the output file name, something like output_1, output_2, etc....

For a quick rundown of looping, take a look at this:

http://www.sqldts.com/246.aspx

Hope this helps,

Alex

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page