wrapping with 1 transaction

Galih 111 Reputation points

Hello expert,

I have 4 steps. each step has its own stored procedure.
If step 2 fails, it will roll back all step, and so on.

how to wrap all of them into 1 transaction?


Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
7,131 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Nandan Hegde 22,721 Reputation points

    Hey, Unfortunately ADF doesn't handle any transactions. You would have to explicitly specify transactions and my guess is you need to create a parent Stored procedure which would in turn call all other SPs and bound them within transactions.
    or create a single SP itslef.

    0 comments No comments

  2. MartinJaffer-MSFT 25,711 Reputation points Microsoft Employee

    Hello @Galih

    @Nandan Hegde is correct. Data Factory does not implement transactions. Transactions are something owned and executed in your SQL server/database.

    The difficulty I see in making the entire thing one transaction, is the multiple lookups outside. I'm guessing you are looping through the lookup output to do inserts using stored procedures.
    There might be another way, or three.
    If you do all the lookups ahead of time, you could use a Script activity to write your transaction statement, and pass in all the data at once. Maybe through the script parameters? The statement can call each of your steps.

    Could you share what dataset type the Lookup is operating on? I wonder if there is a way to use Copy activity instead. Copy activity does have a stored procedure option.