SSIS Local Nested Transactions for Error Handling

We are planning NOT to use MSDTC for transaction managment in SSIS and instead want to use local trsnactions using Begin transaction and commit transaction. From design wise it seems to be OK except one issue. When SSIS package fails in any of the task we have an error event handler which inserts records into errors table. So now what is happening is when SSIS rolls back the transaction it is rolling back the error handling routine as 开发者_运维问答well. But I am NOT sure how I can control this granularly since nested transactions are NOT going to work as I start the transaction at the begining of package execution.

Any thoughts?

Here's an article on MSDN that explains how to use TRY-CATCH blocks with TRANSACTION COMMITT that supports error handling.

Regarding your comment about nested transactions, they don't actually exist in SQL Server. Please see this blog post from Paul Randal.

I switched to MSDTC to handle these transactions and error handling in SSIS. This new approach is very clean, just mark the relevant flows in SSIS as txn required or not and you are done. I marked the error handling routine as 'txn not supported' since I didn't want it to rollback in case of errors. Everything looks fine now except the infrastructure overhead in our case since we are bundling the solutions to individual clients workstations.

Many thanks