Wednesday, August 18, 2010

TransactionOption property in SSIS

The TransactionOption property exists at the
1. package level,
2. container level (e.g. For Loop, Foreach Loop, Sequence, etc.),
3. as well as just about any Control Flow task (e.g. Execute SQL task, Data Flow task, etc.). Transactions work at control flow level and not within a data flow.

TransactionOption can be set to one of the following:

Required - if a transaction exists join it else start a new one
Supported - if a transaction exists join it (this is the default)
NotSupported - do not join an existing transaction

The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. MSDTC also allows you to perform distributed transactions; e.g. updating a SQL Server database and an Oracle database in the same transaction.

1 comment: