I am in the process of Upsizing an Access app that was bloating
because it has to read and import the same text file every 5
minutes.

I am pretty new to SQL Server and am still learing about its
capabilities.

Currently in Access, I have a multi step process in VB to save the
text file as a scratch table and then apply logic to copy/update
fields in an output table.

Pseudocode would be:

Join scratch table to Output table on ID

if Scratch date > Output date and lots of other scratch field tests
here then
copy some scratch fields to Output table
Set some output fields that dont even exist in scratch table.
end if

Convert Scratch date (Serial date type) to normal Date/Time and write
to Output Date



Question: Can/ should I do all this in DTS or should I continue to
use my current code in Access and simply point to the upsized SQL
tables using ODBC.

If I can do this with DTS should I use VB, Activex VB, SQL perhaps?



Thanks


Bill

Personally I do pretty much the same thing

Invariably I import text files "as is" to a Scratch table. I then use
TSQL (Because it is very very fast) to apply my logic.

You can use an ExecuteSQL task to do this
You can obviously pump the data in using a DataPump task. You could
though negate the need for DTS and use BULK INSERT TSQL statement and a
stored proc containing your logic and simply call that through
VB/Access/.NET



Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org