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