Connection manager error does not fire OnError event handler

• damir-777425

  SSC Veteran

  Points: 206

  More actions

  October 10, 2008 at 3:08 am

  #131708

  SSIS 2005.

  I've set up a test package:

  TransactionOption for the package = Required

  TransactionOption for each of the containers in the package = Supported

  TransactionOption for the OnError Event handler on the level of the whole package=NotSupported (to force writing the error in the database in case of transaction rollback)

  There are no other event handlers in the package. FailPackageOnFailure and FailParentOnFailure is False in all components by default.

  There is a data flow task that loads and stores data in Informix database over OLE DB connection.

  When I run the package, it starts distributed transaction. This is the important part of the output:

  SSIS package "testiranje 1.dtsx" starting.

  Information: 0x4004300A at Data Flow Task informix u informix, DTS.Pipeline: Validation phase is beginning.

  Information: 0x4001100A at testiranje: Starting distributed transaction for this container.

  Executing ExecutePackageTask: C:\Documents and Settings\damirk\My Documents\Visual Studio 2005\Projects\laus DWH pod sourceom\laus DWH\laus dwh\Servis_PackageStart.dtsx

  Information: 0x4004300A at Data Flow Task informix u informix, DTS.Pipeline: Validation phase is beginning.

  Information: 0x40043006 at Data Flow Task informix u informix, DTS.Pipeline: Prepare for Execute phase is beginning.

  Information: 0x40043007 at Data Flow Task informix u informix, DTS.Pipeline: Pre-Execute phase is beginning.

  Information: 0x4004300C at Data Flow Task informix u informix, DTS.Pipeline: Execute phase is beginning.

  Information: 0x40043008 at Data Flow Task informix u informix, DTS.Pipeline: Post Execute phase is beginning.

  Error: 0xC001410E at testiranje, Connection manager "Izvor": The connection manager failed to defect from the transaction.

  Error: 0xC001410E at testiranje, Connection manager "Izvor": The connection manager failed to defect from the transaction.

  Information: 0x40043009 at Data Flow Task informix u informix, DTS.Pipeline: Cleanup phase is beginning.

  Information: 0x4004300B at Data Flow Task informix u informix, DTS.Pipeline: "component "OLE DB Destination" (1580)" wrote 1 rows.

  Executing ExecutePackageTask: C:\Documents and Settings\damirk\My Documents\Visual Studio 2005\Projects\laus DWH pod sourceom\laus DWH\laus dwh\Servis_PackageEnd.dtsx

  SSIS package "testiranje 1.dtsx" finished: Canceled.

  I want to catch that error and write it down in the database. I see that it happens in PostExecute phase and that the result is Canceled.

  Data flow task, as well as the two other execute package tasks in this package have DisableEventHandlers=FALSE. Also, in the same package I deliberately made an SQL task make an error to test the error handling mechanism - and it worked. The problem seems to appear only in this case of finishing distributed transaction when the mentioned connection manager doesn't gracefully exit the transaction.

  Can someone direct me what to do?

  Many thanks.

  Damir