Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Issues With DB2 CHARACTER Field Source - SSIS 2008 Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 10:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 7,097, Visits: 12,600
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
Post #1406842
Posted Monday, January 14, 2013 10:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:52 AM
Points: 45, Visits: 106
Yeah, that's the issue...finding the DB2 syntax to strip out the crud. More Googling.
Post #1406843
Posted Monday, January 14, 2013 10:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:52 AM
Points: 45, Visits: 106
opc.three I like your idea, if I can figure out how to cast the field as unicode within the query.
Post #1406852
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse