none
Cannot convert between a unicode and a non-unicode string data types

    Question

  • hi all

    i'm trying to extract data from a view (ORACLEdb) , when i connected the source to the destination, then right clicked and try to execute the destination i'm getting the following error "Cannot convert between a unicode and a non-unicode string data types",

    so i used derive column operator

    and i'm confused please help me , should i change the data type to DT_STR or to DT_WSTR? and what is the code page about?

    Please need help

    Thanks


    SV

    Friday, May 04, 2012 7:00 PM

Answers

All replies

  • and i'm confused please help me , should i change the data type to DT_STR or to DT_WSTR? and what is the code page about?

    Codepages are sets of characters. Chinese characters (codepage 950) are different than western europeen characters (codepage 1252). More info here: http://en.wikipedia.org/wiki/Windows_code_page

    What is the data type of your source? Click on the green Data Flow Path between your source and the derived column and check the meta data. And what is your destination and what is the data type of your destination? Nvarchar (DT_WSTR/unicode) or varchar (DT_STR).

    You can use a CAST in a derived column the create a copy of an existing column, but with an other datatype so that you can use that copy to connect to your destination. You could also add a CAST in your source query to avoid a lot of extra columns. Oracle example: http://www.techonthenet.com/oracle/functions/cast.php


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    Friday, May 04, 2012 8:01 PM
    Moderator
  • Source Information:

    Name Data Type Source Component
    EMPLID DT_WSTR OLE DB Source
    EMPL_RCD DT_NUMERIC OLE DB Source
    LAST_NAME DT_WSTR OLE DB Source
    FIRST_NAME DT_WSTR OLE DB Source
    COI_HRS_CONCAT1 DT_WSTR OLE DB Source
    COI_HRS_CONCAT2 DT_WSTR OLE DB Source
    POSITION_NBR DT_WSTR OLE DB Source
    COI_POSN_DESCR DT_WSTR OLE DB Source
    DUR DT_DBTIMESTAMP OLE DB Source
    TRC DT_WSTR OLE DB Source
    COI_TRC_DESCR DT_WSTR OLE DB Source
    PAYABLE_STATUS DT_WSTR OLE DB Source
    XLATLONGNAME DT_WSTR OLE DB Source
    TL_QUANTITY DT_NUMERIC OLE DB Source
    USER_FIELD_1 DT_WSTR OLE DB Source
    DEPTID DT_WSTR OLE DB Source
    USER_FIELD_2 DT_WSTR OLE DB Source
    USER_FIELD_3 DT_WSTR OLE DB Source
    WORKGROUP DT_WSTR OLE DB Source
    COI_WRKGRP_DESCR DT_WSTR OLE DB Source
    TASKGROUP DT_WSTR OLE DB Source
    COI_TSKGRP_DESCR DT_WSTR OLE DB Source
    TASK_PROFILE_ID DT_WSTR OLE DB Source
    COI_TSKPROF_DESCR DT_WSTR OLE DB Source
    PAYGROUP DT_WSTR OLE DB Source
    COI_PAYGRP_DESCR DT_WSTR

    OLE DB Source

    and my destination data type is "text stream [DT_TEXT]"

    I tried using the DATA CONVERSION operator, but am having difficulty, is there a screenshot examples for this operator?

    Thanks


    SV

    Friday, May 04, 2012 8:10 PM
  • and my destination data type is "text stream [DT_TEXT]"

    I tried using the DATA CONVERSION operator, but am having difficulty, is there a screenshot examples for this operator?

    Thanks

    So you need a conversion from unicode to non-unicode. Do you know the character set from your source? Is it chinees/japanees/europeen/etc? Otherwise you could loose characters.

    Here are some examples of the standard data conversion transformation:
    http://www.bimonkey.com/2009/06/the-data-conversion-transformation/
    http://www.mssqltips.com/sqlservertip/1393/import-excel-unicode-data-with-sql-server-integration-services/


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    Friday, May 04, 2012 8:15 PM
    Moderator
  • Thanks but the source is a oracle db and when i right clicked on the green link and looked at the metadata i didn't see any code page properties.

    Thanks


    SV

    Sunday, May 06, 2012 2:30 PM
  • Thanks but the source is a oracle db and when i right clicked on the green link and looked at the metadata i didn't see any code page properties.

    Thanks


    SV


    It's correct that you don't see a codepage... source is unicode. But I guess it's western/europeen: codepage 1252

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Eileen Zhao Monday, May 07, 2012 8:32 AM
    Sunday, May 06, 2012 5:32 PM
    Moderator
  • Thanks but please bear with me and tell me where am i going wrong, initiall i was getting the following eror when i executed the task:

    TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "EMPLID" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "LAST_NAME" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "FIRST_NAME" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_HRS_CONCAT1" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_HRS_CONCAT2" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "POSITION_NBR" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_POSN_DESCR" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "TRC" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_TRC_DESCR" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "PAYABLE_STATUS" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "XLATLONGNAME" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "USER_FIELD_1" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "DEPTID" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "USER_FIELD_2" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "USER_FIELD_3" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "WORKGROUP" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_WRKGRP_DESCR" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "TASKGROUP" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_TSKGRP_DESCR" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "TASK_PROFILE_ID" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_TSKPROF_DESCR" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "PAYGROUP" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [OLE DB Destination [1082]]: Column "COI_PAYGRP_DESCR" cannot convert between unicode and non-unicode string data types.

    Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB Destination" (1082)" failed validation and returned validation status "VS_ISBROKEN".

    Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

     (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    The destination table has the following CREATE code

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[PEOPLESOFT_HOURS_REPORT_DPR](
    	[EMPLID] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[EMPL_RCD] [numeric](38, 0) NOT NULL,
    	[LAST_NAME] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[FIRST_NAME] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[COI_HRS_CONCAT1] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[COI_HRS_CONCAT2] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[POSITION_NBR] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[COI_POSN_DESCR] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[DUR] [datetime] NULL,
    	[TRC] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[COI_TRC_DESCR] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[PAYABLE_STATUS] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[XLATLONGNAME] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[TL_QUANTITY] [numeric](18, 6) NOT NULL,
    	[USER_FIELD_1] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[DEPTID] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[USER_FIELD_2] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[USER_FIELD_3] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[WORKGROUP] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[COI_WRKGRP_DESCR] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[TASKGROUP] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[COI_TSKGRP_DESCR] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[TASK_PROFILE_ID] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[COI_TSKPROF_DESCR] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[PAYGROUP] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[COI_PAYGRP_DESCR] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF

    Now i added the data conversion transformation and changed the datatyppe to ' STRING(DT_STR) and i'm still getting the same errors, any idea where i'm going wrong?

    Thanks


    SV

    Monday, May 07, 2012 2:02 PM
  • Hi SV,

    Are you changing all the datatype of the columns to DT_STR? This message indicates that a data flow component is trying to pass Unicode string data to another component that expects non-Unicode string data in the corresponding column. Please refer to the following link about it: http://www.mssqltips.com/sqlservertip/1393/import-excel-unicode-data-with-sql-server-integration-services/

    Thanks,
    Eileen

    • Marked as answer by Eileen Zhao Thursday, May 17, 2012 2:35 AM
    Thursday, May 10, 2012 6:19 AM