This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(22.400000000000002, 41%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EG%3C/text%3E%3C/svg%3E)
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?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Hello @Galih ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Martin
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.
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.
@Nandan Hegde & @MartinJaffer-MSFT Yes I also think ADF can't handle any transactions.
Each lookup call same stored procedure, select query a table with a different transaction_type.
continue looping with foreach and insert/update data in stored procedure inside foreach activity.
and all steps must run sequentially.
what is a more effective way?
Well, I don't see why you can't just use a single copy activity instead of looping through lookup results.
I believe when the Copy Activity is used with Stored Proc option on the sink side, the proc is called on each row. This is like what you are doing with the ForEach looping the stored proc.
Since the behavior on the sink is the same (I think,80% confidence), lets look at the source side. The Source also has stored proc option.
It is better to use the copy rather than lookup, because lookup has a 5000 row limit.
So we can substitute the whole lookup+proc loop with a single copy activity. This should have better performance and cost too.
As for transactions, my colleague had a suggestion, @Galih :
load the data into a temp table so you can make the entire insert-temp-to-prod action in a transaction all SQL side. This gives you control, and takes the transaction part away from ADF, while still using ADF to get the data.