Loading data from SQL Server to Snowflake using Data Factory is giving error-CodePudding

I have the following table in SQL Server which I am trying to load into Snowflake using Azure Data Factory:

It has 7 columns:

ID, StartDate, Assigner, Priority, Operation, OldValue, NewValue (THIS IS CREATING iSSUE)

NewValue column has double quotes in it might me because of that. Might be its considering one column as multiple columns.. Not sure what is the exact issue.

I am getting following error:

> ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22000] Found character 'C' instead of record delimiter '\r\n'
      File '93fcbeba-41f7-4824-b8f6-eda4c2965e00/SnowflakeImportCopyCommand/data_93fcbeba-41f7-4824-b8f6-eda4c2965e00_fa1577fb-8544-44a6-8a60-65bd8b2419c5.txt', line 48, character 39
      Row 1 starts at line 2, column "myTable"["$7":7]

Anyone familiar with this error? Guidance is appreciated. Thanks.

CodePudding user response：

See the following link: https://docs.microsoft.com/en-us/answers/questions/559372/delimiter-error-in-copy-actibity-of-adf.html

From that reference:

The issue could be because of the first stage (Source -> staging blob) use csv format and the format serializer failed to escape the escape char in the data eventually causing the data to be invalid.

You could try the below workaround at your end.

Manually edit JSON payload of your pipeline, go to properties -> activities -> {your copy activity} -> "typeProperties", add a flag "escapeQuoteEscaping": true