I faced an error: 'SSIS cannot convert between unicode and non-unicode string data types' even though the source and the destination columns are both unicode (NVARCHAR) data type. The source db and the destination db are on the same server.
The source is a query:
SELECT
CASE
WHEN [Gender__c] = 'Female' THEN 'N'
WHEN [Gender__c] = 'Male' THEN 'M'
WHEN [Gender__c] = 'Nainen' THEN 'N'
WHEN [Gender__c] = 'Mies' THEN 'M'
When [Gender__c] = 'Ei tiedossa' THEN 'Ei tiedossa'
ELSE NULL
END as Gender__c
FROM [database].[table]
Anybody seen the same?
Best Answer
You said that the input column is showing in the advanced editor as non Unicode even though the source data column is Unicode.
Based on your query, I don't think the source data column is Unicode - because the source data column isn't what you think it is.
Gender__C
may be anNVARCHAR
column - but it's not your source column. Your source column is based on the values returned by your CASE statement - that those are allCHAR
/VARCHAR
values.To make the values returned by the CASE statement
NCHAR
/NVARCHAR
, tell SQL that your text literals are to be treated as Unicode values, by putting anN
before the opening single quote:[Since
Gender__c
is anNVARCHAR
, I went ahead and made your comparison valuesNVARCHAR
as well].Try this, and SSIS should recognize that
Gender__c
in the result set is aDT_WSTR
.