DTS for 100 text files

Hello DBA's:

I want to upload 100 text files to a single table on SQL Server 2000.
Records from these text files would be selected on the basis of a where clause.

What would be the best way of accomplishing this? Using DTS, I can do only one file at a time. Is there a faster approach.

Thanks

VivekTill the time you get a better solution you can use some file concatenation utility to join all those files into a single one and do the data load using DTS.

http://www.jddesign.f2s.com/concat-s.htm|||Does this utility join files OR append them one after the other. I want to append the records not join them? Let me know.|||it appends them|||Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?|||sounds like a job for perl (every admins friend) or heaven forbid vbscript.|||You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||Originally posted by Brett Kaiser
Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?

Brett ... no way you can accomplish this without a cursor ...

I challenge everyone to make a set based solution for the same

LOL ... am working on a similar procedure ... need to uload files daily of the form LocationCD_Extracttype_yyyymmdd.txt ... tab seperated files .. any ideas other than bcp and dts ..|||It is a one-time event. I have no experience in shell scripting, unfortunately.
Originally posted by rnealejr
You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||How about this?

Create 2 DTS packages. An Outer and an Inner Package.

The Outer package has an ActiveX script task the uses the FSO to indentify all the files in a directory, then for each file set the text file connection of the inner package to the path of the text file identified in the Outer package. Then call the Inner package which just has a text file connection and a DB connection with a trasnformdata task.
Loop through all the files repeating the above.

This would be very automated and fairly quick depending on the size of the text files. And actually pretty easy to implement.

Stevesql