Transactions in azure-data-factory

Question

Hello,

Am working on one of the adf activity where I am generating the 10 files dynamically with SP outputs using Foreach and Export data. I am trying to implement transactions now where the generation of files should be rolled back if any one of the file generation gets failed. I tried many ways but could not find a way. Do we have such thing to implement, Please advice.
Thank you

Answer 1

Hello @Purushotham Chanda and thank you for your question.

While Data Factory does not have transactions inbuilt, I have created a pipeline which should help, provided you can create an activity to effect the actual roll-back. In this example, I substitute Set Variable activities for the actual stored procs and rollback activity. This is for testing purposes.

In this pipeline, we have 2 loops; one make the change (generate the file in your case) , the other reverts the change (deletes the file).

Inside the Forward-each, there are 2 activities. The first one represents the attempted change (stored proc / creation of file). The second one, records the name of the file when creation is successful. If creation fails, the file name is not recorded, and the iteration and loop report failure.

When the Forward-each reports failure, execution is passed to the Rollback-each.

Inside the Rollback-each is the activity which should revert the change. In your case it would be a delete activity, or another stored proc. I am making the assumption that this activity is incapable of failure. While I can write additional logic for the case of failure, I am trying to keep this simple for now.

The Wait activity named "Only on success" actually has an important role. Its presence causes the pipeline to report a failure status after the Rollback-each executes. Without the presence of an on-success dependency on the Forward-each, the pipeline would report success status even when a rollback was needed.

Attached is pipeline JSON if you would like to experiment. I utilize data type conversion errors to simulate failed stored procs.

9094-rollbackloop.txt