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.
Why is ADF's LookUp not recognizing overall execution status of SQL?
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-
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.
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.
Sign in to comment