Issue while performing copy activity in azure data factory

Question

I have a copy activity source as a response xml .And target as a sql table .
The issue happens when i am trying to copy the xml rows to sql tables.
My sql table is configured to accept float values.But some times xml is passing string as input(for eg:NAN).On that case i need to update my input as Null and update my target table as Null.How can i perform this operation in Copy activity.
Please somebody help me on this.

Please note :I cant use data flows since we were using self hosted vms.

Answer 1

Hello and welcome to Microsoft Q&A @Sampath S

As I understand your source XML has NAN (Not a number) in a float field, but your SQL isn't accepting this, and treating it as string. You want to change them to Null.

I do have an idea to try. In the XML Dataset, there is a "Null value" option. I was thinking to try with putting NAN in that field.