ADF - Stored Proc Activity

Question

ADF - Stored Proc Activity

Santhi Dhanuskodi 325

Hi,

We are using ADF to ETL the data into Azure SQL, and the solution is using SCD type 4. so we have 2 tables TGT and TGTHistory.

I have a stored proc which does incremental load of data, inserts/updates data into these 2 tables.

I would like to know if SP fails for any reason during the middle of execution, say 10 records were inserted and 5 were updated, But there are some more to be inserted. Will ADF automatically rollback the transaction? and Can we see the TGT and history tables in original state how it was before SP execution. Or should we handle rollback/commit in SP?

1 answer

Your answer

Answer 1

Nandan Hegde 34,941 MVP

As you are using Stored procedure do all your biddings/logic, ADF is there just to trigger it but everything is governed by the SP.

So you would have to manage transactions within your SP by begin transaction/commit transaction to ensure rollback in case of any intermediate failures

Santhi Dhanuskodi 325 Reputation points

2024-10-08T07:29:50.4233333+00:00

Are you sure about this? We tested SP activity by simulating a failure condition, But ADF was rolling back transaction automatically. We didnt handle anything in SP for rolback/commit.

Waht about copy activity, transactions would be rolled back automatically incase of failure?

Does MS having a document about this?
Smaran Thoomu 22,195 Reputation points Microsoft External Staff

2024-10-08T10:35:19.4033333+00:00

Santhi Dhanuskodi - Thank you for your question.

When using ADF to execute a stored procedure for ETL operations, ADF itself does not manage transactions. This means that ADF will not automatically rollback transactions if the SP fails during execution. You need to handle transactions within your stored procedure using BEGIN TRANSACTION and COMMIT TRANSACTION statements to ensure that any changes can be rolled back in case of failures.

Regarding your observation that ADF seemed to rollback the transaction automatically during your test, this might be due to the behavior of the underlying SQL engine. If the SQL engine detects an error, it might rollback the transaction implicitly. However, this is not a feature of ADF but rather the SQL engine’s behavior.

For copy activities, ADF does not support transactions. Changes made by copy activities are not transaction-bound and cannot be rolled back automatically by ADF. You would need to implement custom logic to handle rollbacks, such as using staging tables and flag columns to track changes and revert them if necessary.

For more details you can refer these relevant threads: https://learn.microsoft.com/en-us/answers/questions/31313/transactions-in-azure-data-factory
https://learn.microsoft.com/en-us/answers/questions/1375704/rollback-the-changes-in-adf-copy-activity

I hope this helps. Please let us know if you have any questions.
Santhi Dhanuskodi 325 Reputation points

2024-10-09T05:15:16.72+00:00

Is there a chance that copy activity does not finish full data copy into destination, but still it doesnt report any failure or error. If I have 100 records in source csv file, but only 50 records were copied into azure sql using copy, but ADF doesnt report any failure on this.

How do we ensure the whole dataset is copied into target?
Smaran Thoomu 22,195 Reputation points Microsoft External Staff

2024-10-09T06:25:44.9933333+00:00

Santhi Dhanuskodi - Thanks for getting back, regarding your request, I have already shared the information in a different thread that you initiated. Could you please check it there and let me know if there's anything else I can assist you with?
https://learn.microsoft.com/en-us/answers/questions/2099887/adf-copy-activity?comment=question

Share via

ADF - Stored Proc Activity

1 answer

Your answer