Unanswered: ssis package error xact_abort should be on when using transaction option Required
I have a SSIS package when I execute this package it works fine. So far so good.
Now, I need to implement transition on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Bulk Insert with another outstanding result set should be run with XACT_ABORT on.".
To resolve this issue we have tried following things but nothing worked out here.
1. Using “Execute SQL Task” we set the xact_abort option True.
2. Tried to change the property of RetainSameConnection = True, but it throws the error because we are using multiple connection in sequence container.
Can anyone help me on this, how can I use the transaction successfully.