Azure Data Factory - quoted strings from CSV are not treated as NULL values in SQL Server Sink

1
votes

I have a CSV file with all fields quoted with ". I have a Sink to SQL Server and the Copy Data activity is supposed to insert data into a Table directly.

Empty strings from the CSV file are not treated as a NULL value in the SQL table and are treated as empty strings. Unfortunately, I can't find a way to configure the Copy Data activity to change this behavior.

1
csvazure-data-factory
An empty unquoted field is treated as NULL and you can specify what string pattern to treat as NULL in the csv dataset in ADF. But if it's quoted and there's no sentinel pattern for NULL then you get empty string as you describe for "". Do you have control over the csv generation? You could put N/A or something for the nulls and set that as the NULL pattern in the dataset. – Jason Welch
I have no control over the CSV generation, unfortunately. Does it mean that there are no solutions? (except creating a Stored Procedure in the SQL side to replace "" values by null). – David GROSPELIER
Can you share an example row or two? Perhaps it might be possible to copy to a staging format and load from there? – Jason Welch

1 Answers

1
votes

There is no way to do this in the Copy activity.

Some workarounds:

  1. Use Data flow to change the empty string to NULL and save the csv file to Azure blob storage. Then use the Copy activity to copy to SQL server.

  2. Create a SP in your SQL server to change the empty string to NULL, and invoke it in SP activity.

  3. Create a trigger to change the empty string to NULL, and just use Copy activity to copy data.