Debugging SSIS packages

My SSIS package was doing a large ETL from an OLE DB source to a destination OLE DB. I received the error:

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

The package started to error. So I redirected the error output from the destination OLE DB to a flat file.
The error columns indicated the following:

Error Code   -1071607685   Error Column   0   Error Desc   No status is available.

Yet inserting the first row manually of the error output through SQL Management Studio succeeded. What was going wrong? I could insert the row manually, but it was in the error output.

The thing to notice here is that the OLE DB Destination load is using the "Fast Load" process and has a large commit size. That means if one row errors then all of them in the commit size get redirected to the error output.

An improved technique to find the true error rows is to reduce the "Rows per batch" and "Maximum insert commit size" to 10,000 rows. Redirect the error output to another OLE DB load task, but this time instead of setting "Table or View - fast load", select "Table or view". This will load rows individually. Redirect the error output of this to a flat file and this time you will get the exact rows that fail. This technique provides initial speed, but then if an error occurs it tries a finer insert.

In my case it was a foreign key constraint that was not being satisfied.

Footnote
I also discovered by changing the destination to a SQL Server destination that four columns had the wrong data type. The destination row was a SMALLINT whereas the data flow path was using an Signed Int. Some posts indicate that a cast error can cause similar errors and it may occur if the data flow value exceeds the size of the destination column, so it is worth double-checking the data sizes. It shows that the OLE DB destination is less strict than the SQL Server destination.