I am using a DTS package on SQL2000Std to import .csv files into a staging
table using a Transform Data Task and then another Data Transform and add
them to a main table whilst 'grooming' the data (i.e. reformat dates,
normalise telephone numbers, split up name and address fields, add a
'load_date' field, etc.)..

Everything works fine, but it is a bit slow.. (The .csv files have ~150
fields and ~2000 rows, and I can potentially have hundreds to load in at
once..) It currently take about 60 seconds per file.

The first Transform Data Task from text to table is reasonably quick; the
bottleneck is the second transform task which grooms the data..

What I would like to know is which is the most effcient way of doing the
grooming the data? Using the Transform Data Task to do the grooming as it
transfers from one table to another, or running SQL statements on the staging
table?

Am I on the right lines or is there another, better way of doing it?

Thanks

NH