ssis cannot convert between unicode and non-unicode data types even though the source and the destination columns are both unicode

Question

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?

Answer 1

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 an NVARCHAR column - but it's not your source column. Your source column is based on the values returned by your CASE statement - that those are all CHAR/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 an N before the opening single quote:

SELECT 
   CASE
        WHEN [Gender__c] = N'Female' THEN N'N' 
        WHEN [Gender__c] = N'Male' THEN N'M'
        WHEN [Gender__c] = N'Nainen' THEN N'N' 
        WHEN [Gender__c] = N'Mies' THEN N'M'
        When [Gender__c] = N'Ei tiedossa' THEN N'Ei tiedossa'
        ELSE NULL 
    END as Gender__c
  FROM [database].[table] 

[Since Gender__c is an NVARCHAR, I went ahead and made your comparison values NVARCHAR as well].

Try this, and SSIS should recognize that Gender__c in the result set is a DT_WSTR.