Error when redirecting a row in SSIS.
This is a super-easy fix when you come across the following while trying to move error rows from an OLE destination in SSIS to a different destination:
The error row disposition on “input “ OLE DB Destination Input” ()” cannot be set to redirect the row when the fast load option is turned on, and the maximum insert commit size is set to zero.
All that you need to do is change the properties for the OLE destination; when you click on the task, in Custom Properties, Access Mode, switch to OpenRowset from OpenRowset Using FastLoad, or simply add a FastLoadMaxCommitSize greater than one. This will allow you to redirect a row from a destination. So, in the picture below, what I did here was make the change, and then follow the OLE destination with a RowCount task to get the count of “suspend” records, a ScriptTask to translate the row error, and finally, another OLE destination to move the error or suspend rows. This is a snippet of a data flow of a package that I built recently.
Hope this helps,
Lee
a7be0b41-9bc1-47de-bab2-91259202c049|0|.0