stored-procedureserror-handlingazure-data-factory

Getting the error detail of a Stored Procedure Activity in Azure Data Factory

I need to store the error generated during the execution of a stored procedure in ADF. However, the output list of this activity does not provide any value for this. Instead, it shows the DETAILS of the error and is this value the one that I need to capture:

enter image description here

I tried to capture the error with a set variable activity (following some indications), but I've got this error message

enter image description here

I modified the stored procedure to be like this:

BEGIN TRY 
    SET @sql = CONCAT('example_statement', var) 
    EXECUTE sp_executesql @sql;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();
    THROW 500001, @ErrorMessage , 1
END CATCH

Is this possible in ADF?.

Solution

  • Use the below expression to capture the error details of an activity.

    @activity('your_activity_name').error.message

    Example:

    • Stored procedure activity error details.

    enter image description here

    • Capturing the error into a variable using set variable activity upon failure of stored procedure activity.

    @activity('Stored procedure1').error.message

    enter image description here

    • Output of set variable activity that stored the error details in a variable.

    enter image description here