Azure Data Factory - Exception occurred when converting value from type 'String' to type 'Single'

Question

I created an ADF pipeline that has as a Source a CSV file and as a Sink SQL Managed Instance. There is one column called "Use" and this column is of type "Real" in the SQL MI. Unfortunately the CSV file contains some empty values. When I do the Copy Data Activity, I am getting following error:

ErrorCode=TypeConversionFailure,Exception occurred when converting value '' for column name 'Use' from type 'String' (precision:, scale:) to type 'Single' (precision:7, scale:255). Additional info: Input string was not in a correct format.

I tried then to create a Data Flow that would take the CSV file and instead of empty values, assign a null value. The problem is that I can't put the SQL MI as Sink, because of Integration Runtime limitations, so I have to write the file as a CSV and then use it in the Copy Data activity. But even like that, I get the error above.

In the end, I tried to change the data type mapping, but whatever I do, I still keep getting the exact same error. How can I make the Copy Data activity read the null value as a null and not as a string? There are similar questions on Stack Overflow, but all the suggestions are to use a Data Flow with SQL as sink, which unfortunately I can't do.

Answer 1

As you mention in question you have some empty values in CSV file means it could be whitespace ( ) to treat them as null you need to specify the data set property null value.

My input file:

Output:

Or with data flow you can replace the empty space with null string and add the value here in dataset property null value to treat it as null.