wrapping with 1 transaction

Question

wrapping with 1 transaction

Galih 111

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?

MartinJaffer-MSFT 26,161 Reputation points

2022-12-01T16:14:31.217+00:00

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

2 answers

Your answer

MartinJaffer-MSFT 26,161 Reputation points

2022-12-01T16:14:31.217+00:00

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

Answer 1

MartinJaffer-MSFT 26,161

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.

Galih 111 Reputation points

2022-11-21T10:57:16.323+00:00

@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?
MartinJaffer-MSFT 26,161 Reputation points

2022-11-29T18:23:41.407+00:00

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.

Answer 2

Nandan Hegde 36,886 MVP Volunteer Moderator

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

Share via

wrapping with 1 transaction

2 answers

Your answer