BULK INSERT and ROLLBACK

BULK INSERT imports a data files into SQL Server. It’s  range of individual INSERT statements. If the BULK INSERT job fails, there is no ROLLBACK from the commited INSERTS.

Use BULK INSERT with a UDF

One workaround to this problem is to place the BULK INSERT into a user defined transaction. Use this pattern to manage a BULK INSERT process.  This can be further enhanced with  try...catch blocks. Trap errors and report into a log file

BEGIN TRANSACTION
BEGIN TRY
BULK INSERT  MyTable 
FROM 'e:\MyTable.txt' 
WITH (FORMATFILE = 'e:\MyFormatFile.Fmt');
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

Read More on managing Bulk Data

Data Loading Performance Guide

SQL Server - Diary of a DBA – Num 6 – Agnostic Technology

SQL Server architecting write-intensive databases