SSIS Transactions without MSDTC - PART 2 - Excluding Error Logging Event Handler From Transaction

In my previous post, I implemented explicit transaction logic into a SSIS package when the MSDTC service was not available.

One issue this may raise is around the default SSIS event handlers. My package has an 'OnError' event handler, which is called when any error occurs within the package. The event handler simply logs the error to my SQL Error Logging table. This handler was using the same data connection as my main package. Unfortunately this meant that if an error occurred within the package, everything was rolled back, including the logged error message! I needed to find some way of maintaining the logged error but rolling back everything else.

The solution is simple - create a new datasource for the error logging handler:

All other properties should be the same as the data connection used in the main package.
This creates separation between the datasource within the transaction logic and the event handler's datasource.