Using Azure Data Factory to send TSV files to a Snowflake stage, and it is escaping certain characters incorrectly.

Question

I am using a Azure Data Factory pipeline to move compressed TSV files from an on-prem file share to a ADLS share that acts as a Snowflake stage, so we can move it to a Snowflake table.

The problem is when the combination of a backslash and a newline character are found at the end of a line in the file, ADF will escape the backslash character correctly by adding a double-slash ('\'), but it will then 'forget' to escape the newline character. This is leading to many of these type of error coming out of Snowflake stage:

Found character '\\' instead of field delimiter '\t' File '<*file_name*>', line 6134, character 58 Row 6132 starts at line 6133, column "TRANSIENT_STAGE_TABLE"[321]  

My DelimitedText linked service for the Sink has escape character set to a blackslash (). Again, the problem this causes is when I have a backslash at the end of a string value, which exists in my data quite a bit.

This issue seems to be extremely similar to this one, listed below. One of our data engineers already worked on this previously and believe it to be a bug within Azure Data Factory.

https://learn.microsoft.com/en-us/answers/questions/115904/azure-data-factory-adf-escape-character-issue-when.html

Answer 1

Hi @Leif Starbuck ,

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

I reproduced your scenario. used tsv file from ADLS source folder(Data folder in my case) as source and copied it ADLS sink folder(output folder in my case). I don't see errors while performed copy using copy data activity.

Please check below screenshots for detailed idea.

Source File:

Source dataset in ADF:

Sink Dataset in ADF:

Sink file after copy:

Hope this will help. If not, Please share your sample file data to repro with your data too. Thank you.

----------

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