A little history about why one would ever even want no transactions...

Back in the old days when you spent $6k for a server with 32Meg (Yes Meg) of ram so you could have awesome performance, if you imported lots of data it really helped to turn off transaction logging, drop indexes, import data, rebuild statistics and rebuild indexes.

I worked on a data warehouse that imported a million health care claims monthly using a month-end extract. Using BCP to bring data into staging tables resulted in import of those million rows in 1 hour, where previously it took a whole week.

The need for this kind of tuning no longer exists in todays hardware and software capabilities. SSIS can do the kind of imports we did standing on it's head drinking coffee, smoking a non-politcally correct cigarette, reading a book and watching 2 movies.

So, how did we get around the possibilities of corruption?

1) Backup the whole database prior to any BCP activity, start over if it failed.

2) Use a staging database. If the import failed, we simply deleted the database and replaced it with a template. If we could we would break stuff down in batches and pick up where the last batch failed.

BCP was dangerous in those days. You were completely un-protected until it was complete and you performed a complete database backup. But with a data warehouse bringing in millions of rows, there were very few options outside of going to bigger hardware with another database vendor.

So, hats off to MS...look how far you've come baby!