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 Friday, January 11, 2013 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
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
Post #1406051
Posted Friday, January 11, 2013 8:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1406076
Posted Friday, January 11, 2013 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
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.

Post #1406124
Posted Saturday, January 12, 2013 10:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1406445
Posted Monday, January 14, 2013 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
Do I make that change to DT_WSTR on the field in the External Columns or Output Columns?
Post #1406710
Posted Monday, January 14, 2013 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1406711
Posted Monday, January 14, 2013 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
When i try that it says that the field cannot convert between unicode and non unicode string data types.

Post #1406717
Posted Monday, January 14, 2013 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1406729
Posted Monday, January 14, 2013 10:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
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?

Post #1406836
Posted Monday, January 14, 2013 10:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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 () 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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1406839
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse