asked
SSIS - Exclude Event Handler from Transaction
Hi, We have a SSIS package which is wrapped with custom 'BEGIN TRANSACTION' and 'COMMIT TRANSACTION'/'ROLLBACK TRANSACTION' SQL Tasks (as we can't use DTC). On package error, we have an Error event handler which logs to our SQL Error Log table. Unfortunately, when the package errors, the event handler logs the issue successfully but does so as part of the transaction, which is subsequently rolled back in the final SQL Task. I believe the 'Transaction Option' property is only available when using DTC...is there any other way I can exclude the error event handler from the overall transaction, so the logged error is not rolled back? Thanks
IT depends on your overall SSIS package transactions configuration, but you have to set the **Transaction Option** to **Not Supported** on the vent Handler or on the components inside the vent handler. This will ensure, that the code inside the event handler is not part of other transaction.
I have set the RetainSameConnection to false on the datasource and have set the Transaction Option to Not Supported on the event handler and it seems to have worked. Thanks!
Update: Setting RetainSameConnection to false on the datasource when using Native transactions (i.e. declaring the BEGIN/COMMIT in a SQL task) causes the COMMIT step to fail. RetainSameConnection MUST be set to true when using SQL Native Transactions. My workaround was to set this to true for the main datasource, so the COMMIT step would work. I then created a new datasource for the error logging, which was the same as the main datasource, only it had RetainSameConnection set to false.
15 People are following this question.
