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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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