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

Posted on 2012-08-20
Last Modified: 2012-10-02
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
Question by:Bodhi108
    LVL 14

    Expert Comment

    by:nishant joshi
    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..


    Accepted Solution

    With Stored Procedures, I don't believe this is possible so I'm closing this Question.

    Author Closing Comment

    Don't think this is possible to rollback outside of a Stored Procedure...

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now