Using a transaction in a SQL Script recipe

MJL

It seems that Dataiku doesn't detect/report if a transaction fails in a SQL Script recipe. From testing it appears that if the transaction coded to insert rows in a table fails, the records aren't inserted but no error is reported. Is there a way to raise an error if a transaction fails?

My code is basically this;

WHILE LOOP BEGIN
    BEGIN TRAN
        INSERT INTO [T1] ([C1],[C2] [...])
        SELECT ([C1],[C2] [...]) FROM [My_First_Table]

        INSERT INTO [T2] ([C1],[C2] [...])
        SELECT ([C1],[C2] [...]) FROM [My_Second_Table]

        INSERT INTO [T3] ([C1],[C2] [...])
        SELECT ([C1],[C2] [...]) FROM [My_Third_Table]

    COMMIT TRAN
WHILE LOOP END

I want to know if any of the passes through the while loop fails. Ideally a message would be raised and enough information would be written to a log to permit diagnosis, remediation, and reprocessing.

I can imagine that catching the failed transaction could be detected in a try/catch block but how does that get signaled to datakiu as a flow scenario error?

---

Operating system used: Windows