none
how to capture SSIS errors from stored procedure

    Question

  • 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!!!

    BEGIN TRY 

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

    END TRY 

    BEGIN CATCH 

    <sql_statements>

    END CATCH


    Thanks and regards, Rishabh K

    Friday, December 27, 2013 8:54 AM

Answers

  • 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 ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, December 27, 2013 9:05 AM