Search code examples
azurenullazure-data-factoryazure-synapseazure-data-lake-gen2

Handle Null values in Azure Data factory Pipeline

I have created a Pipeline in azure and Importing data from csv to sql table .

While transforming data getting exception when there is null/empty value in csv . I tried to put validation in Null value textbox in Connection , but it is not working . Can anyone help on this

Solution

  • To handle null values in Azure data factory Create derived column and use iifNull({ColumnName}, 'Unknown') expression

    Detailed steps are given below

    Step1: Create dataflow as shown below

    enter image description here

    Step2: Insert CSV file in Source1 with null values enter image description here

    Step3: Now Create derived column and use iifNull({ColumnName}, 'Unknown') expression.

    Here, I have replaced null with ‘Unknown’ as a placeholder

    enter image description here

    Derived column output

    enter image description here

    Step4: Now use SQL database as sink dataset

    enter image description here

    Output:

    enter image description here