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

Question

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 provine any value for this. Instead, it shows the DETAILS fo the error and is this value the one that I need to capture:

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

I modified the store procedure 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?.

Answer 1

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

@activity('your_activity_name').error.message

Example:

• Stored procedure activity error details.

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

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

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