Azure Synapse Data Ingestion From JSON to SQL Data Mapping Problem

SoonJoo@Genting 216 Reputation points
2021-11-20T15:46:17.983+00:00

Hi all,

I am trying to copy data from JSON to SQL database using the Built-in Copy Task. I am trying to mapped the json format to the sql table.

When I use the import schema functions which mapped exactly the name of the json input to the SQL table, the data can sink in without any problem.

151100-screenshot69.jpg

However, if I try to change the mapping name (on the sink side), the data can't be copied over.

151130-screenshot70.jpg

May I know it's is supported or I miss out anything?

Thanks!!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
2,625 questions
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 28,106 Reputation points Microsoft Employee
    2021-11-22T09:33:00.24+00:00

    Hi @SoonJoo@Genting ,

    Thank you for posting query in Microsoft Q&A Platform.

    As shown in below image, If you selected Auto create table and defined different names for Sink mapping, then it will work without any issues.

    151414-image.png

    But, if you try to rename columns names under mappings which is not available in your sink table and you have not selected Auto create table option then you may end up with error. Error in this case make sense as ADF tries to copy data in to a column in Sink which is actually not present.

    If your idea is to rename a column in table before copying then try to leverage pre-copy SQL script option.
    151368-image.png

    Hope this will help. Please let us know if any further queries.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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
    No comments

0 additional answers

Sort by: Most helpful