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

DTS and SP issue

I have a DTS that export a CSV file, and the sql is a store procedure(SP).
The DTS work fine if the SP is a single select statement. However, if there
are insert statements within the SP, the DTS failed to execute. How can i
solve this problem?
If I change the SP to sql function, it works. Unfortunately, I have a update
statement to be execute so that i can't change SP to sql function.
Thanks in advance!
MartinHi
I amn not quire sure what you are trying to do, but here are a few things
you may want to try!
Remove temporary tables and use derived tables or functions.
Use a CASE statement instead of updating the table.
SET NOCOUNT ON at the start of the procedure.
Split the job into several steps to do the pre-processing first
John
"Atenza" wrote:

> I have a DTS that export a CSV file, and the sql is a store procedure(SP).
> The DTS work fine if the SP is a single select statement. However, if ther
e
> are insert statements within the SP, the DTS failed to execute. How can i
> solve this problem?
> If I change the SP to sql function, it works. Unfortunately, I have a upda
te
> statement to be execute so that i can't change SP to sql function.
> Thanks in advance!
> Martin
>
>|||oh, it works now! Thank you so so much!!!!! Thank you for your support!!!
Martin
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:FA99D8E2-72F3-45C5-B40C-C3F2F648CD0E@.microsoft.com...
> Hi
> I amn not quire sure what you are trying to do, but here are a few things
> you may want to try!
> Remove temporary tables and use derived tables or functions.
> Use a CASE statement instead of updating the table.
> SET NOCOUNT ON at the start of the procedure.
> Split the job into several steps to do the pre-processing first
> John
> "Atenza" wrote:
>
procedure(SP).
there
i
update|||Hi
For the record, which option(s) made it work?
John|||SET NOCOUNT ON at the start of the procedure. Thx again!
Martin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1113546952.977706.271200@.l41g2000cwc.googlegroups.com...
> Hi
> For the record, which option(s) made it work?
> John
>