azure data factory get lookup activity error output

Question

Im my pipeline, there is only one lookup activity and a stored procedure activity following it when the lookup fails.

The lookup sends a query like

    select 1/count(*) as result from sometable

The stored procedure activity calls a stored precedure with a parameter named 'error'.

Basically I want this lookup to fail when count(*) is 0, and then I want to capture the divide by 0 error message from the activity output and use that as the input parameter for the stored procedure.

The output from the lookup when count(*) is 0 is:

    {
        "errorCode": "2100",
        "message": "Failure happened on 'Sink' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Upload file failed at path 37b1ec96-be95-4010-8547-10387fc407a3\\result.,Source=Microsoft.DataTransfer.Common,''Type=System.Data.SqlClient.SqlException,Message=Divide by zero error encountered.,Source=.Net SqlClient Data Provider,SqlErrorNumber=8134,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=8134,State=1,Message=Divide by zero error encountered.,},],'",
        "failureType": "UserError",
        "target": "Lookup source count",
        "details": []
    }

So in the stored procedure activity, I want to put the message from above to the parameter of the stored procedure. I have tried @activity('Lookup source count').output.message for the input parameter. But get this error:

    {
        "errorCode": "InvalidTemplate",
        "message": "The expression 'activity('Lookup source count').output.message' cannot be evaluated because property 'message' doesn't exist, available properties are ''.",
        "failureType": "UserError",
        "target": "log fail Lookup source count",
        "details": ""
    }

I have also tried many other options but none of them works. How can I get the error message from the lookup when it fails?

Answer 1

Using, "Add dynamic content", use this as your SP parameter value

@activity('<name of your lookup>').Error.Message