how to capture SSIS errors from stored procedure


  • HI All,

    I have a job which contains a SSIS package. Also there is one user defined stored procedure which actually starts the job by using the statement 

    EXECUTE msdb.dbo.sp_start_job
             @job_name = 'errorcheck'

    I want to capture the errors encountered in SSIS from stored procedure (which you can think of as calling exception). 

    I just don't know how to return the error from SSIS to stored procedure execution level. What I tried is very simple try catch construct which is obviously not working for me..

    Any help would be appreciated!!!


    EXECUTE msdb.dbo.sp_start_job
             @job_name = 'errorcheck'

    END TRY 




    Thanks and regards, Rishabh K

    Friday, December 27, 2013 8:54 AM


  • You cant return error details as it is from SSIS package

    The best way to do this is to make use of a logging table.

    inside SSIS enable Event handlers for OnError event for tasks and inside have an execute sql task to log the error details to your created log table. Use packagename and executionid values to identify the package and execution for which you had the errors. You can use system variable for this (System::PackageName, System::ExecutionId). At end of the package execution check for count from the logtable where tablename = <your table name> AND ExecutionID = <your execution id>  The error message and description can also be got from inside event handlers using System::ErrorCode, System::ErrorDescription etc variables.

    ie use query like

    IF EXISTS (SELECT 1 FROM Table WHERE packageName = <packagename> and ExecutionID = <ExecutionId>)
    SELECT ... FROM LogTable
    this will give you error details from the package

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Friday, December 27, 2013 9:05 AM