Search code examples
ssistransactionsssis-2012

SSIS Transactions: Does Supported option create a new transaction if it does not exist?

Required starts a new transaction while Supported joins an existing transaction.

However, if a transaction does not already exist, then does the Supported option create a new transaction?

This MSDN link suggests that it does not; where as this Microsoft training video at 36:36 says that it does.

Solution

  • The msdn documentation and the video are consistent

    • Required Make a transaction
    • Supported Enlist in available transaction
    • NotSupported Ignore any available transaction

    At the 36 minute mark, the video is discussing SSIS Checkpoints which is more like a bookmark for the package. They record the last executed step for a run. For anything but the most trivial of packages, I advise against using SSIS Checkpoints as they're flaky, unreliable and cantankerous.

    Instead, design your packages with restartability in mind. Task X fails - how does your package deal with it if it is restarted? Can it clean up any hanging/incomplete work? Can it identify the work has been done and skip it/perform no work?

    The comments indicate

    The slide at 36:36 reads as: "Supported joins an existing transaction or starts a new one". So is this correct or not?

    Incorrect slide

    The slide is incorrect. If you don't believe the people that wrote the documentation, read the entirety of the internet on the topic and you'll discover everyone saying the same thing. Either this youtuber is a savant or they are wrong. You can evaluate the truthfulness of my answer and everyone else by firing up the distributed transaction coordinator, DTC, and watch as the package runs under Supported and Required transaction levels. You'll be able to observe that DTC has work to do under Required and none in Supported/NotSupported