Luis Cazares (10/20/2014)
The problem is with your way to handle errors. You need to have a try-catch block for each statement.I guess that you have something like this:
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table2
FROM '\\SomePath\File2.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
You could change it to this:
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
BEGIN TRY
BEGIN TRAN
BULK INSERT Table2
FROM '\\SomePath\File2.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table2
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table3
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table4
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
It looks more like this where I pass one file at a time to the proc. That file loads a whole bunch of tables.If an error occurs anyhere in the proc id like log error, rollback that file transaction and move to the next file instead of stopping the process.