How to Get Error Output from and OLE DB Command Destination

I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.

However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.

I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).

Any help would be greatly appreciated.

To get rows down the error output you change the ErrorRowDisposition property for the input to be redirect row. Have you done this? If not go the last page of the Advanced Editor, select the Input, and change the ErrorRowDisposition property.|||

I reviewed your suggestion of changing the ErrorRowDisposition value to RD_RedirectRow and that is where the issue is. I view the Advanced Editor for the OLE DB Command destination object and expand the Input Columns under OLE DB Command Input and see several input columns. However, the problem is every one of those columns has an ErrorRowDisposition=RD_NotUsed and the field is greyed out so I am unable to change the setting. Would I need to change any settings in the source or data conversion objects to allow these values to be editable?

|||Select the input and stop there, don't expand the columns. The setting is on the input which is in effect the parent for the columns.