Issue while performing copy activity in azure data factory

Sampath S 81 Reputation points
2022-08-27T04:51:38.72+00:00

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.

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
696 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,338 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,011 Reputation points
    2022-08-29T17:04:02.83+00:00

    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.

    235810-image.png