Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issues With DB2 CHARACTER Field Source - SSIS 2008


Issues With DB2 CHARACTER Field Source - SSIS 2008

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
paul.j.kemna
paul.j.kemna
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 151
Yeah, that's the issue...finding the DB2 syntax to strip out the crud. More Googling.
paul.j.kemna
paul.j.kemna
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 151
opc.three I like your idea, if I can figure out how to cast the field as unicode within the query.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search