SSIS - can you have a Distributed Tranasaction across multiple connection managers?

I have one task that executes Stored Procedure A which inserts and deletes data based on reading another table on another server.  

I have another task that executes Stored Procedure B that updates a table on a different server using a different connection manager.

I would like to have that under one commit but everything I read says that it has to be under one connection manager.  If I put everything in one Stored Procedure and use the Linked Server to do the update then it is way too slow!  So having two separate tasks that are performed under two different connection managers is working very quickly but I would like to make sure that Stored Procedure B gets committed only if Stored Procedure A is successful.  And ideally, to rollback if there is a problem with Stored Procedure B.

Thanks, Abbi
Bodhi108Asked:
Who is Participating?
 
Bodhi108Author Commented:
With Stored Procedures, I don't believe this is possible so I'm closing this Question.
0
 
nishant joshiTechnology Development ConsultantCommented:
Hi Abbi,
you can use sql try catch block in first exexcute sql task.
if it raise error then rollback it in catch and raise error from execute sql task using RaiseErro() function..


Thanks,
Nishant
0
 
Bodhi108Author Commented:
Don't think this is possible to rollback outside of a Stored Procedure...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.