How to determine faulty row/column in SSIS transformation

Question

I'm running a DTSX package to move data from SQL to Postgres using the PGOLEDB Native Postgres driver and I'm receiving an error (below). How do I read this error and determine what row/column is causing the issue?

I'm receiving the following error (PGNP-SE-1.4.3076):

OnError,POSTGRESDEV,DOMAIN\USERNAME,Data Flow Task 9,{1EE44D97-78DD-4175-8162-2520654A750A},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "ERROR: invalid byte sequence for encoding "UTF8": 0x96
CONTEXT: COPY TransHist, line 390
".

OnError,POSTGRESDEV,DOMAIN\USERNAME,full_export_new,{79EBE819-217A-4AB2-BF58-45A6D6A3B4B8},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "ERROR: invalid byte sequence for encoding "UTF8": 0x96
CONTEXT: COPY TransHist, line 390
".

OnError,POSTGRESDEV,DOMAIN\USERNAME,Data Flow Task 9,{1EE44D97-78DD-4175-8162-2520654A750A},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071607767,0x,SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (95)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (95)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

OnError,POSTGRESDEV,DOMAIN\USERNAME,full_export_new,{79EBE819-217A-4AB2-BF58-45A6D6A3B4B8},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1071607767,0x,SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (95)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (95)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

OnError,POSTGRESDEV,DOMAIN\USERNAME,Data Flow Task 9,{1EE44D97-78DD-4175-8162-2520654A750A},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 40 - TransHist" (82) failed with error code 0xC0209029 while processing input "Destination Input" (95). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

OnError,POSTGRESDEV,DOMAIN\USERNAME,full_export_new,{79EBE819-217A-4AB2-BF58-45A6D6A3B4B8},{F27E0BD1-8850-4497-BD9B-BDAFA7BDA401},10/4/2012 8:46:13 AM,10/4/2012 8:46:13 AM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 40 - TransHist" (82) failed with error code 0xC0209029 while processing input "Destination Input" (95). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

UPDATE: I think the encoding error is a red-herring. When I run this it fails on row 1000 (successfully writes 999 rows). If I change the source to a view that limits the results to less than 1000 rows (say 500) it succeeds (but obviously not all the data is moved). If I change the destinations connection string to BULK_INSERT=500...it will fail on row 500 and if I change the view to less than 500 it succeeds.

UPDATE 2: Setting BULK_INSERT to 0 fixes the issue...I'm sure that has negative effects on performance though.

Solution

A quick way to determine which row(s) are responsible for the error is to error behavior from the default of "fail component" to "redirect rows" and then pipe the error output to a flat file.

For your particular error, the key message is:

"ERROR: invalid byte sequence for encoding "UTF8": 0x96

Google'ing this string brings back a few hits indicating a string encoding issue. Here's a link from the google-results back to stackoverflow.

You could try using a data conversion task to specify the correct encoding in the data flow task or you could make adjustments on the destination system (but I'm not really familiar with Postgres so can't help you there)

Comments

[Webjedi]

I think this error is a red-herring. I updated the question to show that it always fails at whatever the BULK_INSERT size is on the connection string. Thoughts?

[Admin]

This is the problem with Microsoft error codes - we have to google them! WTF !!! I spend more time hunting down causes of errors than I do actually developing. FO Microsoft !

[Nick.McDermaid]

Perhaps its because the batch doesn't commit till you hit the BULK_INSERT size so it doesn't notice the bad record until then. If you set BULK_INSERT to 1 does it let you find the row? It's curious that it works when set to 0