paul.j.kemna (1/14/2013)
A user on another forum suggested that I dump the error output into a text file.I did this and I believe it has shed some light on the problem. It appears that the records that are failing have some unknown character in place of the apostrophe?
examples:
THAT?S
I?M
CAN?T
DIDN?T
These words appear as such in the actual source system:
THAT S
I M
CAN T
DIDN T
I have no idea how this happened.
So now that I have found the problem, how do i account for these unknown characters and ignore them so that the rest of the text comes over?
The escaping of characters with a ?-mark implies there is character in the data that is not on the codepage for your collation, likely Windows 1252. I bet it is some kind of control character embedded in the DB2 database. These are usually something below ASCII 32 (space).
I would recommend casting the data on the way out of DB2 so that SSIS sees it as Unicode so you can being the data in as DT_WSTR. Nothing gets escaped in Unicode so you should not have the problem after that. Once you have the data into the SSIS pipeline as Unicode you can cleanse it, then using a Data Conversion Transform you can bring is back down to DT_STR before pushing it down the pipeline further.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato