Using Azure Data Factory How to Format Decimal Field In JSON

Question

I have a Copy Data job where the source is JSON and the sink is a MySQL database. My source file is JSON. I've successfully loaded several JSON files using Copy Data task in ADF. However, I have a JSON file that has a field with very long decimal: "UW Purchase Price / Unit": 396464.64646464644. <-- this fails because the corresponding MySQL field is decimal(19,4).

How do I convert UW Purchase Price coming from JSON so that it can be inserted into the decimal(19,4) data type?

Answer 1

Hello @Jon Carlisle ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is how to get the around with the error , please do let us know if its not accurate.
From the second post , it is clear that the the column type on the uw_purchase_price is INT and you are trying to add a decimal to that hence the error .

You can try a few things here .

1. If you want to let the copy happen for all the other rows and let this one row to put aside . You can use the fault tolerance .
   
2. The copy activity does not allow type conversion , so you can copy the data in a staging table and then use the stored procedure activity to move the data from the staging table to the main table .
3. You can use the dataflow to copy the data . Dataflow supports transformation and so you can convert 396464.64646464644 to 396464 .

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators