ChandaPurushotham-5866 avatar image
0 Votes"
ChandaPurushotham-5866 asked MartinJaffer-MSFT commented

Transactions in azure-data-factory


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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered MartinJaffer-MSFT commented

Hello @ChandaPurushotham-5866 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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Could you please let me know if my suggestion helped? (mark as answer)

Also, I came up with an enhancement.

My previous solution does provide the ability to keep only full sets of files, but it leaves 'footprints' of the files being created and erased.
If the loops are made to write to a 'staging' storage, and then the 'Only on success' activity with copying from the 'staging' to the final location,
then we remove the 'footprints'. The collection of files only get written to your final location if all of them successfully are written to the 'staging' location.

This enhancement does use an additional storage account, but it makes your intended destination much cleaner.

0 Votes 0 ·

Since we have still not heard back from you, we will assume you found your own resolution. If you found a solution, would you please share it here with the community?


0 Votes 0 ·