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

George, Nitin Mathew 25 Reputation points
2024-02-01T11:34:55.94+00:00

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 User's image
  • Contents of SQL Stored Procedure and it's CORRECT behaviour when induced with an Error User's image
  • Wrong & misleading behaviour by ADF activity that marks an internally failed response as a success User's image 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.
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 107.2K 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.