Handling and Logging Sproc errors with SSIS

I frequently use SSIS to schedule some maintence tasks using stored procedures. I want to use transactions to control my data and want to use SSIS to control my work flow. To accomplish this I use try..catch and Raiserror in the stored procedure and SSIS logging to keep track of the errors.

For example I use this sproc :

CREATE PROCEDURE MoveData
AS
INSERT INTO History.ReportTable
SELECT * FROM Reporting.ReportTable
WHERE reportdate < cast(dateadd(DAY,-2,GETDATE())as date);
 
 DELETE FROM Reporting.ReportTable
 WHERE reportdate < cast(dateadd(DAY,-2,GETDATE())as date);

But I want to control the data so all or nothing is commited to the data, since SQL Server 2005 you can use the try..catch function and use transactions with that, so adding that maken:

CREATE PROCEDURE MoveData
AS
BEGIN TRANSACTION;
BEGIN TRY

INSERT INTO History.ReportTable
SELECT * FROM Reporting.ReportTable
WHERE reportdate < cast(dateadd(DAY,-2,GETDATE())as date);
 
 DELETE FROM Reporting.ReportTable
 WHERE reportdate < cast(dateadd(DAY,-2,GETDATE())as date);

END TRY
BEGIN CATCH

 IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

This stored procedure makes sure that when the insert of delete fails for any reason it will be rollbacked. But my sproc will just run and not return a error, my SSIS execute SQL task won’t know anything went wrong. That’s were RAISERROR comes in. Adding that to the sproc makes:

CREATE PROCEDURE MoveData
AS
BEGIN TRANSACTION;
BEGIN TRY

INSERT INTO History.ReportTable
SELECT * FROM Reporting.ReportTable
WHERE reportdate < cast(dateadd(DAY,-2,GETDATE())as date);
 
 DELETE FROM Reporting.ReportTable
 WHERE reportdate < cast(dateadd(DAY,-2,GETDATE())as date);

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

— Get the current error
SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    — Use RAISERROR inside the CATCH block to return
    — error information about the original error that
    — caused execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, — Message text.
               @ErrorSeverity, — Severity.
               @ErrorState — State.
               );
 IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

You now can create your own error handling, whether in a workflow or use the SSIS logging to monitor and log your errors, like I do.