SSIS 'Column' and 'Parameter' cannot convert between unicode and non-unicode string data types

Question

I have a Data Flow Task that does this:

1. From two different OLE DB Sources, run a SQL Command to select some data
2. Merge Join the two DB sources
3. Lookup to a different SQL DB Source, check if the data already exists or not
4. Based on Lookup Match Output vs. Lookup No Match Output, run INSERT statement with different input parameters.

The problem is, during the INSERT, it's complaining about Column 'a' and Parameter '1':

Cannot convert between unicode and non-unicode string data types.

I looked up this StackFlow suggestion to change the Column 'a' to (DT_WSTR) string. However the column has already been set to that data type.

• SSIS Convert Between Unicode and Non-Unicode Error

Column 'a' is actually not grabbing directly from the database, but instead a constant value

SELECT 'abc'+ FORMAT(GETDATE(),'yyyyMMdd') + 'zz208874' AS a

Any idea what I'm doing wrong?

Answer 1

I think the main problem is how the query is parsed in the OLEDB Command and how the SQL Server database engine defined the parameter data type.

Try using a CAST() function to avoid that:

 SELECT CAST('abc'+ FORMAT(GETDATE(),'yyyyMMdd') + 'zz208874' as VARCHAR(255)) AS a

Or, if you are looking to convert to unicode:

SELECT CAST('abc'+ FORMAT(GETDATE(),'yyyyMMdd') + 'zz208874' as NVARCHAR(255)) AS a

or, you can define strings as Unicode:

SELECT N'abc'+ FORMAT(GETDATE(),'yyyyMMdd') + N'zz208874' AS a

For more information about parameter data type parsing issues, you can check my answer on the following question:

• Date calculation with parameter in SSIS is not giving the correct result