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
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
I don't see a forum for SSIS 2008, so I guess I will post here??

I have an OLE DB connection to an IBM DB2 source. I am trying to bring in some data from a query on a few tables. One of the fields I am trying to import is defined as a CHARACTER with a length 70.

When I run the package, I get the following errors:

[OLE DB Source [1]] Error: There was an error with output column "CC_COMMENT" (743) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

[OLE DB Source [1]] Error: The "output column "CC_COMMENT" (743)" failed because truncation occurred, and the truncation row disposition on "output column "CC_COMMENT" (743)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

I have tried adding a Data Conversion and converting to both DT_STR and DT_WSTR with Lengths of 70 and 255.

I have tried inserting into both a varchar(max) field as well as a char(255) and an nvarchar(max)

In every case, it fails.

Any ideas? It makes we wonder if there is some kind of wonky character in the field somewhere, hence the "no match on the code page".


PK
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8373 Visits: 19500
paul.j.kemna (1/11/2013)
I don't see a forum for SSIS 2008, so I guess I will post here??

I have an OLE DB connection to an IBM DB2 source. I am trying to bring in some data from a query on a few tables. One of the fields I am trying to import is defined as a CHARACTER with a length 70.

When I run the package, I get the following errors:

[OLE DB Source [1]] Error: There was an error with output column "CC_COMMENT" (743) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

[OLE DB Source [1]] Error: The "output column "CC_COMMENT" (743)" failed because truncation occurred, and the truncation row disposition on "output column "CC_COMMENT" (743)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

I have tried adding a Data Conversion and converting to both DT_STR and DT_WSTR with Lengths of 70 and 255.

I have tried inserting into both a varchar(max) field as well as a char(255) and an nvarchar(max)

In every case, it fails.

Any ideas? It makes we wonder if there is some kind of wonky character in the field somewhere, hence the "no match on the code page".


PK


SSIS quite often seems to use a default width of 50 for strings.

Try opening up the advanced editor for your datasource and looking at the suspect column data types there. The error is happening before you can possibly fix it using data conversions - it's happening right at the start of your data pipeline.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
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
According to the advanced editor, on the OLE DB Source Output, External Columns and Output Columns, both say string[DT_STR] with a length of 70.
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: 8247 Visits: 14368
From the error message the package appears to be failing on the OLE DB Source so I am wondering, is it even reaching the Data Conversion you said you added? How about the attempt to insert into the VARCHAR(MAX) column you mentioned?

Try changing the OLE DB Source component such that it processes the incoming data as a DT_WSTR. The second part of the error message says "or one or more characters had no match in the target code page".

__________________________________________________________________________________________________
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
Do I make that change to DT_WSTR on the field in the External Columns or Output Columns?
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: 8247 Visits: 14368
Output. External is just what SSIS thinks the source is.

__________________________________________________________________________________________________
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
When i try that it says that the field cannot convert between unicode and non unicode string data types.
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: 8247 Visits: 14368
I do not work with DB2 much and am not sure how great the driver is or how well the engine reports its own metadata when SSIS asks. Does DB2 have something analogous to CAST or CONVERT? You may want to try casting that column on the initial retrieval. SSIS is detecting the column as DT_STR but it seems you're getting data outside that domain from DB2 CHARACTER.

__________________________________________________________________________________________________
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
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?
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8373 Visits: 19500
paul.j.kemna (1/14/2013)


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?



Change your OLE DB source component so that it issues a query to retrieve the data, rather than just bringing the table back en masse.

In the query, use whatever DB2 syntax is necessary (:alienSmile to strip out the crud before it gets to SQL Server.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
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