sql-serverunicodessisnon-unicode

SSIS package having problem with datename(dw,datum) converting to varchar

I am moving data into a DW using SQL Server SSIS and have the following SQL to populate one dimension

SELECT DISTINCT
cast (datename(dw,datum) as varchar(10)) as veckodag 
FROM XXXXX.dbo.Bought

as I have VARCHAR in the target column I need to CAST/CONVERT

Question how to convert Unicode to Varchar?

I get

*> Validation error. Datum OLE DB Source

[1]: Column "veckodag" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0*

Solution

  • In SSIS use "Data Conversion Transformation" inside data flow