SSIS Control Flow

  • Comments posted to this topic are about the item SSIS Control Flow

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Nice question, thanks! It made me think about and better cement my understanding of transactions in SSIS, which is a good thing.

  • Good question.

  • This is reference to this Question :

    The Sequence Container contains three Execute SQL Tasks (Execute SQL Task 1, Execute SQL Task 2, and Execute SQL Task 3) connected by OnSuccess Precedence Constraints: Execute SQL Task 1 ==> Execute SQL Task 2 ==> Execute SQL Task 3. And Execute SQL Task gets failed, then the updates of Execute SQL Tasks 1 and 2 will be rolledback or committed?

    <p/>

    It is answered as Commited.

    <p/>

    How it'll be commited..? It'll be rolled back right?. If these Exectue SQL Tasks are placed directly in the Control Flow, then updates of Execute SQL Task 1 and 2 will be committed when Task3 gets failed. But All three are placed in the container. So they will work as a Unit. All the tasks in Container will be failed, When one Task gets failed inside the container. So Updates of Task1 and Task2 will be rolled back. But It is answered as Commited.

    <p>Please Explain..</p>

  • @dsvasudevan: I believe there is no explicit transaction, so the 3 tasks do not belong to the same transaction.

    On-topic: great question!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Good question...

    The logic can be well understood as below:

    For Parent : Transaction option = Required

    For Child : Transaction option = Supported/Not Supported

    Raunak J

  • Nice question. Took a minute to work out the logic in my head.

  • Nice Question!

  • nice question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Nice question. It helps me awake to right settings and error handling for my simmilar SSIS task :w00t:.

    Petr

    __________________________________
    Standing on the shoulders of giants ...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply