Unable to update string value to null in target database during Copy Data Activity in Azure Data Factory

Being Real 0
Viewed 97

Source: Xml API(Json Format) Target: sql server

[{
"captain":{
"row":[
{
"@name": "carlo",
"@runs":50.0,
"@mom": null,(target -double)
"@mos": nan(target-double)
},
{
"@name": "David ",
"@runs":500.0,
"@mom":5,
"@mos":1
}
]
}
}
]

My target database, sql server will only accepts double values. Whenever string value get passed it should update as null in target. Otherwise it will produce error. What should I do here for solving my issue. I got one solution.

In the source dataset of xml api, there is a field called Null Value. There we can mention which string should i update as null in target. But the issue is if one string(null) comes in source ,it will updates an null in target .But at the same time another string comes(nan) ,I am not able to update AS NULL in target database. What should I do here?

Pls note: I cannot use dataflows since our organisation not using azure ir.

azure-data-factory azure-data-factory-2
1 Answers

First copy the XML API response to a csv file with null value as null. Now, in this CSV file, the columns (double type), have null and nan values. One thing we can do is convert all the nan records to null records. Then read the final CSV file which only contains null values (by specifying source dataset nul value as null and sink as sql table).

Please look at the following demonstration.

  • Lets say I have data as shown below after writing the XML API response to CSV (demo column is float in target table. My target table has the name as tp so I mapped it properly).

enter image description here

  • Now, we need to convert all the nan values to null. So, create a copy data activity where source is the above csv file and sink in a new csv file (or the same file but in different datasets, one for source and one for sink. Then above csv will be overwritten). I used the same CSV file as my source and sink.
  • The following is the source dataset configuration where my file path is data/MyFolder/sample1.csv and my null value is nan

enter image description here

  • The following is the sink dataset configuration where my file path is data/MyFolder/sample1.csv and my null value is null. This converts all nan to null.

enter image description here

  • The copy data succeeds and creates the following output:

enter image description here

  • Now, Create a new copy activity where source dataset file would be the above csv file with null value as null. You can see the values are read as null in the below image.

enter image description here

  • Use sink as your SQL table and run the pipeline. enter image description here

  • The activity would run successfully and insert the values properly. The output of my target table when I use the following query:

select * from demo2

enter image description here

  • The output when I select rows where tp is null:
select  *  from demo2 where tp is  null

enter image description here

Saideep Arikontham0
Related