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?
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