Issues With DB2 CHARACTER Field Source - SSIS 2008

  • 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

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

  • 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

  • Do I make that change to DT_WSTR on the field in the External Columns or Output Columns?

  • 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

  • When i try that it says that the field cannot convert between unicode and non unicode string data types.

  • 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

  • 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?

  • 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 (:alien:) to strip out the crud before it gets to SQL Server.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • Yeah, that's the issue...finding the DB2 syntax to strip out the crud. More Googling.

  • opc.three I like your idea, if I can figure out how to cast the field as unicode within the query.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply