Why is ADF's LookUp not recognizing overall execution status of SQL?

George, Nitin Mathew 25

Not happy with the response of LookUp activity in ADF while invoking a SQL Stored Procedure and I believe this is a bug that needs to be fixed by MS ADF Product Team and where do I/you raise it?

I understand that the backend (especially if it used .NET Connectors) would not catch this if they rely on the Result pane outputs and the work around needs to check if the Message also ends in success as below- User's image

Without this, the ADF behaves in a very misleading way that continues with the flow assuming the execution status as Success. This is another indication why ADF is still not a fully developed product and contradicting with way of SSIS.

A simple ADF Pipeline that uses a LookUp activity to invoke a Stored Procedure as below
Contents of SQL Stored Procedure and it's CORRECT behaviour when induced with an Error
Wrong & misleading behaviour by ADF activity that marks an internally failed response as a success As long as SSMS rightly captures the overall execution status of query as an Error while ADF is unable to is something that I am unable to accept as a correct nature of lookup task.

George, Nitin Mathew 25 Reputation points

2024-02-20T07:35:51.0766667+00:00

As per @Erland Sommarskog I had raised a SR with MS and below is the statement from MS-

ADF, being designed to work with result sets, will consider the execution successful as long as it receives the expected structure of the result set, regardless of the content of that result set.

I am unable to accept that and so I had performed the below scenario just to prove that MS's ideology goes wrong! I had to perform an experiment, forcing SQL to end a processing with an error but still making SQL return some partial rows until the error. This experiment should be clear enough to the Product team to prove their statement wrong! Consider below code- where am simply generating 1000 rows and have a condition just to induce a row time error; and as per the last remark from MS team, I had commented the SELECT return of error as well. Note that the output technically should give me 1000 rows if the induced error was absent. SQL Code:

SQL Execution Output on Message:

SQL Execution Output on Result:

ADF Execution Summary:

The Result window too indicates the 'Query completed with errors' that implies that the SQL does throw the inner exception which the Lookup activity is failing to consider it. Moreover the SSIS (again a product from Microsoft) would fail these scenarios rightly whereas ADF doesn't. I would still like to highlight on my concern 'This is another indication why ADF is still not a fully developed product and contradicting with way of SSIS'.
Erland Sommarskog 110K Reputation points

2024-02-20T22:01:37.79+00:00

What happens if you try the above, without the TRY-CATCH? Do they still report success? You may think that there is no difference, but there is a subtle difference in how the exception appears on the TDS channel. I cover this in the last section in my long series on Error and Transaction Handling in SQL Server, The Virtue of Getting All Result Sets. (I should add that originally learnt this from Dan Guzman, who has also written about it.) As for design, I entirely agree with you. Saying that everything went find when it did not is a cardinal sin. It is very clear that this can lead to silent data loss.
George, Nitin Mathew 25 Reputation points

2024-03-05T09:40:47.45+00:00

To all Solution Architects, please understand that SSIS is not legacy and ADF is not a full-fledged tool; I have a reference to showcase why ADF is not fully reliable and have this use case where ADF should be replaced. This requirement or behavior is very basic and Microsoft ignores it!

Can somebody help me with any escalation or references to the right team?

1 answer

Erland Sommarskog 110K Reputation points

2024-02-01T22:13:04.2733333+00:00

The place to submit bugs for Azure Data Factory would be here: https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c What happens if you use ;THROW rather than RAISERROR to re-raise the error? The two are different in that ;THROW aborts execution and RAISERROR does not.
Please sign in to rate this answer.
George, Nitin Mathew 25 Reputation points

2024-02-02T05:33:18.5066667+00:00

@Erland Sommarskog i had updated the question-

As long as SSMS rightly captures the overall execution status of query as an Error while ADF is unable to is something that I am unable to accept as a correct nature of lookup task.

However, let me check using a throw & let you know the behaviour.

George, Nitin Mathew 25 Reputation points

2024-02-05T16:29:26.6433333+00:00

@Erland Sommarskog THROW doesn't seem to work. And the link you had shared seems to be for feature request and I hardly see a prompt response/acknowledgement from MS Team unless the votes are higher. Is there any GitHub?

Erland Sommarskog 110K Reputation points

2024-02-05T21:22:04.64+00:00

I will have to admit that the suggestion to use THROW was a shot in the dark. I don't use Azure Data Factory, so I cannot test myself.

The site I posted to would be best to report the task. While you may find this to be a bug, risk is that the ADF team thinks it is "by design" and it is indeed a feature request.

I don't know of any Github site, and I would not expect any. But you could search.

If this is a blocking issue for you, you would need to open a support case.

George, Nitin Mathew 25 Reputation points

2024-02-07T06:39:10.8633333+00:00

Thank you @Erland Sommarskog for your response, let me try raising a Support Request.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Why is ADF's LookUp not recognizing overall execution status of SQL?

1 answer

Your answer