Azure Data Factory - Copy Data - Error mapping doble quotes from SQL to Snowflake

Flavio A. Casas 1 Reputation point
2021-09-22T15:25:44.88+00:00

When I make a pipeline with Copy Data, everything goes well, until any string field get a double quotes into the value.
This is the error that show me:

11111_2.jpg

I'm running just the row where I've the error, and the problem is in field "bullet4" that contains the value:

2222_14.png

Für noch mehr Spielspaß hol Dir die <a href="http://www.bigfishgames.de/download-spiele/8956/haunted-manor-herr-der-spiegel-sammleredition/index.html\">Sammleredition</a>!

If I do a replace function into the select statement of the source, like this:

replace(cast(bullet4 as nvarchar(2000)), '"', '') as bullet4

Everything goes well, but the stuff is, that this a migration, so in the beginning this only happens with 2 tables, but now those are 4. I would like to put some function to erase the double quotes from any field that got string datatype, or do something generic that I could use as validation to prevent this error.

All the cases that I found are for .csv or json as Source, but being SQL the options are limited.

Also check use a Data Flow, but the configuration is a far effort compared with Copy Data.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,074 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,056 Reputation points
    2021-09-22T21:34:03.047+00:00

    Hello @Flavio A. Casas and welcome to Microsoft Q&A.

    Have you tried enabling staged copy in the settings? This sometimes clears up formatting issues, and can improve performance.
    I think there is a fair chance it will help with the quotes, without having to erase them.

    0 comments No comments