question

ljaf_sql avatar image
ljaf_sql 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
sqlssistransaction
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ljaf_sql avatar image
ljaf_sql answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ljaf_sql avatar image
ljaf_sql answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.