My preference for BCP is ... familiarity
The thing I like (which may well be achievable with all the others, I don't have knowledge enough to know) is the level of control we get. Apart from speed our biggest problem is "What went wrong"; I haven't had close contact with SSIS for Donkeys' Years, but back then I saw Client SQL Admins spending all morning figuring out what had gone wrong with overnight Data Imports, and "fixing them". In fairness the SQL Admins the Clients employed were not high calibre, so their Imports probably lacked any sort of useful diagnostic logging / capture.
We typically use BCP for a bunch of ETL file imports, overnight, in a Batch file, using a command looking something like this:
in particular the "-e" parameter to catch errors, the "-o" to catch Output (typically the number of rows processed, in each batch) and the Redirect of screen output, each provide diagnostic information, in appropriate circumstances, that no single output does.
We can use the presence of the "-E" Error file as an indication that there is a problem, along with the ERRORLEVEL that BCP returns.
We use "-N" for Native data format whenever we can (i.e. SQL-to-SQL transfer) which solves the parsing of CSV / dates / etc. problems, but when using CSV if there is a muckup in the data then I want to know the row where it occurred, and if the import can continue, ignoring that row, so much the better.
What if the connection is duff, or the import file has zero bytes (we treat that as an error condition,its only ever happened to us when some earlier process had a disk full).
I wish I could remember all the other errors we have had, because a comprehensive list would greatly help in the building and testing of these String & Gum solutions - e.g. allowing deliberate simulation of all potential errors
For batch processes that provide the same named files, once-a-day, we want to be sure that timestamps are appropriate. We also want to be able to control "flow" of the files - so maybe:
- Check that files exist in source location and "look OK" - e.g. filesize and date
- Move the files to the IMPORT location (so that any newly arriving files don't interfere)
- Perform the BCP import
- Move the files to a DONE location - e.g. we cycle 10 folders DONE01 ... DONE10 so we have the last 10 data import files still available when User reports a problem (for some users we cycle 100 folders!!)
- Have a means of discovering that the process failed. e.g. on START if there are already files in the IMPORT location either its currently already running or the previous import failed.