Import (flat file) into SQL Server I've seen a couple of questions about the subject backfiring so I'll try to be specific about my concern...

I have an old (MS Access) frontend I'm trying to rebuild and I left the part that troubles me most for last, so here I am. 
The task is to 'grab' a file (used to be an XLS), now it would more that likely be by-product of it, say CSV for the sake of the example.

So this is my first steppingstone, I'm concerned about the result of my import, according to Pinal Dave BULK INSERT is a non atomic operation:


  If there is any error in any row it will be not inserted but other
  rows will be inserted.


This is not appealing to me since I tend not to trust the user input, I want more control thus being able to trigger an error and not importing any part of the file would be better. I'm not sure if this effectively wipes the possibility of using BULK INSERT for this process?

I also tried reading line by line and inserting using a LOOP cycle, this is effective but unfortunately its takes to long even after optimizing my code I just went from 15 minutes to 4, and that's only for 850 records.

I'm a bit mad that the process worked so smoothly when running from the Access frontend. That was because of the DoCmd.TransferSpreadsheet VBA method. This allowed for fast XLS import into a temp table and from that one the execution carried on updating tables.

I would like to know what other options have guys tried when importing data from flat file sources into SQL Server. Admittedly I'm a bit confused at this point.

Any help would be appreciated.
BULK INSERT into VARCHAR(MAX) in my opinion, then you shouldn't have errors and from there you can manipulate the data as needed and run standard SQL to make sure it fits within your requirements before actually placing it in the destination table. Just a suggestion but it's how I've done it in the past.