I'll start off by declaring total agreement with Geoff.

But some other posts were very intelligent so are worth discussing.

I very much liked the first comment (from Keith) because it makes sense if you have to live with silly constraints that preclude BCP (anyone with a datacomms background would like it, it fits the real world so well) - but I would recommend using a hyperexponential backoff alogrithm (at least for the first few steps), rather than a simple power series. This means that your batch size is 1/2 the original size at the first failure level, 1/8 at the second, 1/64 at the third, 1/1024 at the fourth, and so on [in practise, I have dropped back to exponential if I hit the "and so on" - after 1/1024 I have 1/16384, and so on]. This is often [sometimes?] better than compromises like using 10 as the division factor instead of 2 (often, but not always - we are into statistics and probability theory here, not simple arithmetic with definite answers). I didn't much like Dan Linstedt's first comment, perhaps because I just naturally thought that the constraints on the original import would be minimal and not include most business rules (maybe if I read the article again I will see something I didn't notice on first reading that implies all the business rules are used in the initial import - I can't imagine anyone without pointy hair envisaging such a system, so I would have had to suspend my belief in the sanity of DBAs in order to notice it) - and anyway, as MelS-512196 said, anything/everything breaks and needs exception handling. But I didn't think Dan's comment was mostly marketing speak as suggested by sknox.

Geoff's comments really answer it all - those constraints that forbid use of BCP (or BULK INSERT if we are looking at a server later that SQL 2000) are just plain crazy, and BCP is the basis for a sensible and cost-effective solution. oriented databases I came back to the real world of relational databases (and had to throw IBM's DB2 out of the shop and bring in SQLS so that rapid development and prototyping with incremental release was feasible) - but I have never used BULK INSERT, so I have to take Geoff's word for it that it will do the job. Anyone back then who suggested "requirements" that would preclude use of DTS where needed would have been looking for a new job pretty quickly!]