Error not raising from BULK INSERT IN try block

Question

hi i have written the below code inside a try block but does not raising error to catch block . but stand alone bulk insert generating error messages.

BEGIN TRY

SET @sql = 
                '
                    BULK INSERT dbo.vw_Data_Import_ISO_CSP_OPMHC_Premium--<-- table view
                    FROM ''' + @SourceFilePath + '''
                    WITH ( FIRSTROW = 2, FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'' );
                '

EXECUTE (@sql)

END TRY
BEGIN CATCH

        SELECT 
            @error_number = ERROR_NUMBER(), 
            @error_message = ERROR_MESSAGE(), 
            @error_line = ERROR_LINE()
        SELECT @error_description = 'T-SQL error number ' + CAST(@error_number AS VARCHAR(10))  + ' on line ' + CAST(@error_line AS VARCHAR(10)) + '.'

END CATCH 

Answer 1

You are not raising an error in the CATCH block, only assigning a variable value. Try adding RAISERROR:

BEGIN TRY
    SET @sql = ' BULK INSERT dbo.vw_Data_Import_ISO_CSP_OPMHC_Premium --<-- table view 
FROM ''' + @SourceFilePath + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'', MAXERRORS = 0 );'
    EXECUTE (@sql);
END TRY BEGIN CATCH
    SELECT 
        @error_number = ERROR_NUMBER(), 
        @error_message = ERROR_MESSAGE(), 
        @error_line = ERROR_LINE();
    SELECT @error_description = 'T-SQL error number ' + CAST(@error_number AS VARCHAR(10))  + ' on line ' + CAST(@error_line AS VARCHAR(10)) + '.';
    RAISERROR(@error_description, 16, 1);
END CATCH; 

In SQL Server 2012 and later, the CATCH block can be simplified using THROW:

BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

EDIT:

As suggested in the comments, a non-zero MAXERRORS specification is useful in situations where some records in the source cannot be parsed and may be skipped. The default is 10 if not specified.