Mapping issue from JSON to Azure SQL table in Azure Data Factory

Question

I am trying to map a JSON output into Azure SQL table.
Currently, my JSON file looks like this:
(I validated the JSON format of the output file at https://jsonlint.com/ , and it looks fine ("Valid Json")).

This is setting that I did for JSON file.

This is screenshot when I Preview source data (JSON). So I am trying to move data from JSON format to SQL table.
I am not sure why it shows as this (having row with "Prop_0".:

What could be the reason that JSON file does not show up with each columns (like "VENDORID", "VENDORNAME", "TOTALPAID" etc.) when I try to connect "Copy data" and map Json file (in Blob storage) to Azure SQL table?

Thanks.

Answer 1

Hello @KingJava ,
Thanks for the ask and using Microsoft Q&A platform .
The reason you are seeing this because when you have created the source dataset , I am sure you created a wrong format type ( most probably CSV) , please select JSON and it should work fine .

When I test this with CSV , I get the below error ( similar to yours )

When i go with JSON it works fine

Please do let me know how it goes .
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

Answer 2

Hello @KingJava ,
Since I had created a dummy data to reproduce the issue . I went ahead and tried to check on the ingestion also . Once you have fixed the JSON issue called out above , assuming that you have the sql table already created , please navigate to the Mapping tab and click on the Advance editor and set the mapping .I am sharing the screenshot below for clarity .

The records should flow to Sql .

Please do let me know how it goes .
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