Issue sinking from Azure Data Factory v2 to Azure Synapse Warehouse with Greek letters

Question

We are trying to import data that contains Greek letters into Azure Synapse Analytics (Data Warehouse) via Azure Data Factory v2. We are getting replacement characters for the Greek characters.

Collation for Data Warehouse: SQL_Latin1_General_CP1_CI_AS

I created Temp Table with two columns for testing. One Column has the collation needed.
CREATE TABLE [GRANTSDM].DimTermList_CC;

I limited my data flow to focus on this particular transform. (Source data coming from JSON file UTF-8 encoding)
At the Data Preview for the Sink, the data looks correct for the 'collTerms' column. (Just focusing on the first row)

When I run in the pipeline for the data flow to sink to the table, the characters gets replaced on the column with the collation (collTerms)

The sink needs to be similar to this SQL insert statement
INSERT INTO [GRANTSDM].[DimTermList_CC] (terms,collTerms)
VALUES ('γδ T cells', N'γδ T cells');

I tested this out in another temp table via SSMS

Is there some thing I can do about the Sink to help it behave like the SQL Insert statement (prefixing the string with the letter 'N')? Or does the column need to be derived differently to insert this correctly?

Thank you for any input.

Answer 1

Hi @Cullen, Cory ,

Thank you for posting query in Microsoft Q&A Platform.

When we work with non-english data we should consider using datatype as NVARCHAR(). N stands for unicode. This allows us to store non english characters.

I tried your scenario and able to get data correctly. Please check below screenshots.

Script used to create table

CREATE TABLE [dbo].[DimTermList_CC3]  
(  
    terms NVARCHAR(64),   
    collTerms NVARCHAR(64) COLLATE SQL_Latin1_General_CP1253_CI_AI  
);  

Hope this helps.

------------------------

Please consider hitting Accept Answer button. Accepted answers help community as well.