Azure Data Factory - Lookup activity - How to access error data? (SQL error)

Question

I am using Azure Data Factory and Azure Database Instance.

I have a Data Factory Lookup activity that calls a procedure that can fail. In the CATCH block THROW is called when a failure occurs. I can see the 'output' of THROW when inspecting the error popup in the Azure Data Factory client. I am currently unable to access this 'output', it is not output as far as I can tell.

I have tried (each line is an attempt):

@activity('activityName').output.firstRow.error

@activity('activityName').output.firstRow.errors

@activity('activityName').error.message

@activity('activityName').errors.message

@activity('activityName').errors.number

@activity('activityName').ErrorCode

@activity('activityName').Errors

When inspecting the error popup:

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed, 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=A database operation failed with the following error: 'blabla', Source=,''Type=System.Data.SqlClient.SqlException, Message=blabla, Source=.Net SqlClient Data Provider, SqlErrorNumber=245, Class=16, ErrorCode=-2146232060, State=1, Errors=[{ Class=16, Number=245,State=1,Message=blabla,},],'

The data is clearly there, just I cannot access it and I am finding the documentation lacking in this case. There is comprehensive documentation on accessing the error output from a CopyActivity but not LookupActivity.

What I am trying to achieve is logging errors. In some cases, the procedure succeeds and needs to submit output to Data Factory which is why I must capture the error data from the THROW; I have been unable to fail this task by raising the error again with THROW after returning a SELECT to Data factory.

KarthikBhyresh-MT KarthikBhyresh-MT · Accepted Answer · 2021-09-17T10:51:30

Usually you can access an activities complex output as below:

@activity('*activityName*').output.*subfield1*.*subfield2*

the error body is in the below format:

"value": {
        "errorCode": "2100",
        "message": "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid object name 'SalesLT.Customr'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'SalesLT.Customr'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=208,State=1,Message=Invalid object name 'SalesLT.Customr'.,},],'",
        "failureType": "UserError",
        "target": "Lookup",
        "details": []
    }

You can use this in add dynamic content:

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

Here is an example, where you can use Add activity on method to select the output stream that continues onto the next activity.

A stored procedure results an error:

The string variable errorOutput now holds the error message!

Further, you can access each element of error message by using dot operator.

 1. @activity('<name of your lookup>').Error.errorCode
    
 2. @activity('<name of your lookup>').Error.failureType

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

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

Alternately, if you want to ensure an activity is executed only when its previous activity (lookup activity in your scenario) was successful, you can also use If condition activity on "Completion" stream.

Different causes may lead to this error. Check the list for possible cause analysis and related recommendation.

Azure Data Factory - Lookup activity - How to access error data? (SQL error)

1 Answers