Transactions mode

I have a package that uses only transformation tasks like Lookup, Merge and Slowly Changing Dimension. I would like to rollback all the INSERTs or UPDATEs done if an error occurs (like dup key).

I understand the different transactions modes (Explicit, Autocommit, Implicit) but I'm not sure how to use it within an SSIS package.

How can I associate a transaction mode to a connection Is there a property for it How do I issue the BEGIN, COMMIT or ROLLBACK TRANSACTION when I'm using Explicit Transactions with an OLE DB connection

Thanks!

Gilles

Answer this question

Transactions mode

S_R_M
I have a package which consists of 3 Execute SQL Tasks -

1) Drop old database & Restore a new one
2) Run DDL
3) Run DML

My task was to put all of these in a transaction. I started using the "TransactionOption" property for this package to be "Required" and each child tasks also were set to the same transaction option property of "Required". However, after running the package, it errored out saying Task 1 cannot be in a transaction (which is logical) so, I removed the transaction required property from the first task and kept it on for the remaining 2 tasks. In addition, I have also set up a failover strategy, where in if these tasks were to fail, the package should restore the previous working copy of the backup. To test this scenario, I deliberately created an error in the DML task, so logically only this task should rollback, instead it rolls back the entire transaction and to my horror the failover step is not executed as well.

Is there something which I am not doing correctly

How do I go about this

Thanks,

Deepak.
StiNKy
See Jamie's blog entry here:
http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx
jimwill
SSIS supports transactions itself. These are basically distributed transactions, and require DTC, but can be very useful as they can span tasks, connections, and even packages.

Set the TransactionOption property to Required to enlist a task/container/package in a transaction.

SSIS: Package transactions
(http://blogs.conchango.com/jamiethomson/archive/2004/12/14/456.aspx)

You can also use T-SQL scope transactions, provided all objects use the same SQL connection. Issue your BEGIN and COMMIT statements at the relevent points. You will need to set RetainSameConnection on the connection, so that you keep the same session, and can manage the connection. There was recent thread on this topic, but I cannot find it! This search is a waste of space, and Google is failing me.
Varghese
Thanks, I had figure out what is described in Jamie's nugget but what I was looking for was TransactionOption=required. I looked at it before but I wasn't sure.

Thanks also to Michael Entin who wrote me ...
--------

SSIS manages transactions on control flow container (task, loop, sequence or package) levels, rather than doing it per-connection.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

If a task/sequence/loop or package has a property TransactionOption=Required, SSIS creates a DTC transaction and the task uses this transaction when performing operations that support transactions. Child tasks inherit this transaction if their TransactionOption is either Required or Supported. SSIS automatically commits the transaction when container that started it finishes successfully, if it fails SSIS aborts the transaction.

So if you have a single data flow task, you can set TransactionOption=Required either at the package level, or at the data flow task level – it should not matter. If you have multiple tasks, setting it at the package level will cause all tasks to share the transactions, setting it at tasks level will create a per-task transactions.

Thanks,

Michael.
---------------------------
Transactions mode

Answer this question