I am currently doing this same thing in SQL 7 and SQL 2K. Please read whole article.

Our older process uses DTS and BCP to load data and process it. This process also uses TEMP tables (I know BAD BAD BAD) I have rewritten that particular piece of the code.

I have created several different VB apps that look for the text files and write a record into SQL (ADO using stored-procedure). If the file is found it writes a flag stating TRUE else FALSE.

I then have the DTS packages scheduled to run around each other that look in the log to see if the file is there, if we have processed today, etc.. If all conditions are TRUE then we perform the INSERT.

I have created a LINKED SERVER to handle the text files. I have also created a schema.ini file to handle column names and data types. This way SQL handles ALL processing.

The old process ran on average 1 hour 30 to 1 hour 50 minutes. Using the LINKED SERVER and with more validations the process takes 5 minutes 20 seconds.

just a different way of looking at the issue.

I can post sample code if you would like to look at it.