SSIS Package: convert between unicode and non-unicode string data types

Question

I am connecting to an Oracle DB and the connection works, but I get the following error for some of the columns:

Description: Column "RESOURCE_NAME" cannot convert between unicode 
and non-unicode string data types.

Value for RESOURCE_NAME:

For Oracle: VARCHAR2(200 BYTE)
For SQL Server: VARCHAR(200 BYTE)

I can connect to the Oracle DB via Oracle SQL Developer without any issues. Also, I have the SSIS package setting Run64BitRuntime = False.

Mike Henderson · Accepted Answer · 2016-07-28T11:00:38.583

12

The Oracle data type VARCHAR2 appears to be equivalent to NVARCHAR in SQL Server, or DT_WSTR in SSIS. Reference

You will have to convert using the Data Conversion Transformation, or CAST or CONVERT functions in SQL Server.

edited Jul 28 '16 at 11:00

answered Jul 15 '13 at 19:54

Mike Henderson

1,305
15
27

score 3 · Answer 2 · edited Feb 17 '15 at 19:58

3

If the package works in one machine and doesn't in other; Try setting the NLS_LANG to right language, territory and character set and test the package.

[Command Prompt]> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

edited Feb 17 '15 at 19:58

Mike G

4,232
9
40
66

answered Jan 23 '15 at 07:44

Jay Akhawri

31
1

score 2 · Answer 3 · edited Oct 18 '18 at 11:37

2

The easiest way around this to to open the SSIS package in notepad (the dtsx file) and do a global find and replace of all instances of validateExternalMetadata="True" with validateExternalMetadata="False".

note: we encountered this issue when connecting to an Oracle 11g database on Linux through SSIS.

edited Oct 18 '18 at 11:37

SteveC

15,808
23
102
173

answered Aug 22 '13 at 14:40

rip747

9,375
8
36
47

I believe `.dtsx` is correct. Make a back-up of the file before you touch it. – Smandoli Dec 22 '14 at 17:10

score 1 · Answer 4 · answered Jul 16 '13 at 12:21

1

on oledb source ->advanced editor options->input/output columns->output columns->select RESOURCE_NAME column and change Data type as DT_WSTR and length also u can change as required

answered Jul 16 '13 at 12:21

USER9999

19
1
2

score 1 · Answer 5 · answered Nov 21 '17 at 16:48

1

You can use SQL command in SSIS and use CONVERT or CAST. If SSIS still gives you an error its because of the metadata. Here is how you can fix it.

Open the Advanced Editor.
Under the Input and Output properties, Expand Source Output.
Expand Output columns
Select the column which is causing the issue.
Go to Data Type Properties and change the DataType to your desired type DT_STR, DT_Text etc.

answered Nov 21 '17 at 16:48

shockwave

3,074
9
35
60

1

This saved me! Thanks – Earth Engine Dec 13 '17 at 00:30
Where can I find the advanced editor for an SSIS package? – Patrick Schomburg Aug 12 '19 at 12:02
@PatrickSchomburg - In the Data Flow task, drag the ODBC source from Other Sources and right click the source. You'll find Advanced Editor. – shockwave Aug 12 '19 at 21:25
This - the solution I'm used to - doesn't work for me. I set the column to DT_WSTR (correct length). OK. Go back into Adv. Editor: it's flipped back to DT_STR. This is even before execution or validation. ValidateExternalMetadata on or off makes no difference. It's the MS Oracle driver being a pain, I think... – SebTHU Oct 20 '22 at 15:22

score 1 · Answer 6 · answered Dec 14 '18 at 21:22

1

You can just double-click on the "Data Conversion" block in the Data Flow and for every item change it to: "Unicode String [DT_WSTR]"

Works

answered Dec 14 '18 at 21:22

user2860427

65
1
2
5

score 0 · Answer 7 · answered Sep 27 '16 at 13:36

0

If everything failed from above. Create a table variable and insert the data into it. Then select all records as source. use SET NOCOUNT ON in the script.

answered Sep 27 '16 at 13:36

Amar Jyoti Lahon

1

score 0 · Answer 8 · answered Aug 24 '20 at 22:18

I encountered a very similar problem even using SQL Server rather than Oracle. In my case, I was using a Flat File as a data source, so I just went in to the Flat File Connection Manager and manually changed the column type to be a Unicode string:

I don't know if this would fix your problem or not, but it helped me - hopefully someone else will be helped too. (I was inspired to try that by this previous answer to this question BTW, just to give credit where credit's due).

SSIS Package: convert between unicode and non-unicode string data types

8 Answers8

Linked