Transactions in azure-data-factory

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