SSIS Package Troubleshooting

Question

I'm working with an SSIS Package that pulls data from a DB2 source, runs through a conversion process (unicode stuff) and then stores the data in a SQL table. From the error information below, I have been able to determine that there is some kind of special characters in the DB2 file/table. What I do not know is how I can narrow down which specific record has the issue. There are about 200,000 records in the DB2 file and I need to know which one is specifically causing the issue.

Is there a way to query the DB2 source looking for "special characters"? Is there a way to have the SSIS package show me which record it is failing on?

Error: 2009-07-15 01:32:31.19
Code: 0xC020901C
Source: Import MY APP Data DETAIL [2670]
Description: There was an error with output column "COLUMN1" (2710) on output "OLE DB Source Output" (2680). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Answer 1

DB2 has a built-in function called HEX() that takes in just about any expression of any type and returns a VARCHAR of the hex representation of each byte. You can also specify any binary value as a literal by prepending it x', for example: x'0123456789abcdef'

If the problem is coming from a single-byte character, you could find it by building up temp table of all single characters from x'00' to x'ff' and seeing which ones appear in each row of your DB2 data. You could also add some code to the utility that converts the data for Unicode so it will scan the DB2 records for any anomalies.