I've used the newest tools extensively in building a data mart over the last year and have come to the opinion that....forget all of the activex, data driven query, all that stuff, because it's a row by row, cursor-based type programmatic operation, and for my money, I choose set-based operations. Especially for working with millions of records.
What I would do is create a staging table and simply import the data as is. Create a stored procedure that does a SELECT INTO into another table and complete your data scrubbing and formatting at this time from the staging table. It will run much faster than a row-based operation (this is of course if you don't have one of the great new ETL tools such as Informatica).
Row by row processing is nowhere at my place.
Hope this helps some