How to Get error output of Stored procedure Activity in ADF Pipeline

Question

I am trying to log error message and pipeline details to sql table.

if my Stored procedure fails with some reason in pipeline, I am unable to get actual error message to capture.

I also tried to put try catch in my SP, but no use.

Any other method to capture the error details of failed SP activity in ADF

Answer 1

You can use this enter image description here

You can use this: connect the red output of your activity with a stored procedure that will fill an sql table as soon as it is Failed

Answer 2

You can raise the error in procedure in catch block as follows.

CREATE PROC [dbo].[usp_fail_proc] 
AS BEGIN

BEGIN TRY 
    
    // Script

END TRY

BEGIN CATCH 
    DECLARE @ErrorNumber INT = ERROR_NUMBER()

    DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
    DECLARE @ErrorProcedure NVARCHAR(128) = ERROR_PROCEDURE()
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
    DECLARE @ErrorState INT = ERROR_STATE()

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH 
END

And in ADF error can be accessed in failure path as follows. In the activity connected to the failure path we can extract the error message as @{activity('sp').error.message}