Issue with Unicode Conversion

  • I am using SSIS 2008. The package retrieve data from one source DB table and insert to other DB table.

    The source table have one column 'comments' and sometime it returning more than 8000 char.

    To dealt with this I made some changes to my stored proc which actually pulls the data from source.

    I am using Varchar(Max) now instead of Varchar(8000) for comments field and also made changes to target table EMPDetails' Comments field.

    Target table EmpDetails Comments field has also Varchar (Max) datatype now instead of Varchar(8000).

    I opened my SSIS package and I noticed some warning message on DataFlow task and once i clicked on the task

    It prompt me to automatically fixed the errors and i clicked for Yes.

    I believe this alert was because I changes the source and destination table Column DataType.

    After this all the package looks fine to me and I saved the package.

    Now when I am running my SSIS package it throws below exception.

    Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode conversion failed".

    The "input "OLE DB Destination Input" (53)" failed because error code 0xC020907B occurred.

    The source data will contain large string value below format

    AA1;20110809;AA55;1009.0000;C15;20110809;AA4;1010.0000;

    Please let me know the resolution for this issue.

  • What are the datatypes of the source and target columns (in the physical tables)?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • What data type did you pick in the source component in the data flow?

    DT_TEXT?

    If the source is unicode, it should be DT_NTEXT.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (9/11/2014)


    What data type did you pick in the source component in the data flow?

    DT_TEXT?

    If the source is unicode, it should be DT_NTEXT.

    More to the point, if the data you are copying is unicode data copying it into a varchar(max) column won't work unless the unicode data can be expressed in the default codepage associated with the collation for that column, which I find is usually not the case. so perhaps you should try NVARCHAR(MAX) instead of VARCHAR(MAX).

    Tom

  • All.

    Apologies for delay response.

    Into SSIS the data type for these column is

    text stream [DT_TEXT]

    The below string I am retrieving sand inserting to database table.

    AA1;20110809;AA55;1009.0000;C15;20110809;AA4;1010.0000;

    Is this Unicode string? Please advise what changes I should apply to resolve the issue?

    The database stored procedure and table has VarChar(Max) data type to process the above string.

  • Karan_W (9/26/2014)


    All.

    Apologies for delay response.

    Into SSIS the data type for these column is

    text stream [DT_TEXT]

    The below string I am retrieving sand inserting to database table.

    AA1;20110809;AA55;1009.0000;C15;20110809;AA4;1010.0000;

    Is this Unicode string? Please advise what changes I should apply to resolve the issue?

    The database stored procedure and table has VarChar(Max) data type to process the above string.

    Although it does not seem to contain any characters which cannot be represented as VARCHAR(), we cannot tell you whether the string is Unicode or not - that depends on what is storing the string.

    As both source and target columns are VARCHAR() and SSIS is using DT_TEXT, you should not get any Unicode warnings for this data.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks Phill.

    Any suggestion what I should try now to resole this issue?

  • Karan_W (9/26/2014)


    Thanks Phill.

    Any suggestion what I should try now to resole this issue?

    Source is not Unicode, target is not Unicode.

    So something in the package must be trying to use Unicode.

    Follow the data path through the package using advanced editor etc to find out where this is happening.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 8 posts - 1 through 7 (of 7 total)

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