Sunday, November 03, 2013

"[OLE DB Destination [48]] Error: The input "OLE DB Destination Input" (61) cannot be set to redirect on error using a connection in a transaction

Problem

This issue raises if you try to use TransactionOption Required in SSIS while you have error rows to redirect between an OLE DB Source and an OLE DB Destination.

Solution

Instead try using a 3 Execute SQL tasks in the Control Flow with the first one before the data flow task with SQLStatement BEGIN TRANSACTION.

Put the rest of the 2 Execute SQL tasks in the Control Flow below the Data Flow Task with the precedence Constraint set for one as 'Failure' with the SQLStatement ROLLBACK TRANSACTION and the other one with the precedence Constraint set as 'Success' with the SQLStatement COMMIT TRANSACTION

Note: Also make sure to set the RetainSameConnection property on the appropriate ConnectionManager to True.

No comments:

Post a Comment