Survey banner
Share your feedback on the Dataiku documentation with this 5 min survey. Thanks! TAKE THE SURVEY

Using a transaction in a SQL Script recipe

MJL
Level 2
Using a transaction in a SQL Script recipe

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

0 Kudos
0 Replies

Labels

?
Labels (2)
A banner prompting to get Dataiku