Import JSON Files into SQL Server Using Datafactory

Question

I have tried to parse a format of a json file stored in the datalake which has its internal content organized as follows:

But I would like it to be saved to sql like this:

I want to emphasize that I cannot use get metadatas or lookups since it is a process that will have millions of records to be ingested into SQL, I would appreciate if someone knows how to achieve this format conversion in a json file.
Thank you very much.

Answer 1

Hi @Samuel Pastrana Bedoya ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question.
As I understand your issue, you are trying to perform data transformation and load the data into SQL target table . Please let me know if my understanding is incorrect.

You can consider using mapping dataflow in ADF to achieve the requirement.

Kindly check the following video for reference: https://www.youtube.com/watch?v=eA2OZ56e53I&list=PLsJW07-_K61IFEbcrImrHeLaS6C_voe0E&index=6

Also, consider providing the full source data for us to reproduce your scenario to provide better help as in the screenshot, I can't relate any relation between the source data as well as the expected output.

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

------------------------------

• 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 @Samuel Pastrana Bedoya

You can perform this by following steps mentioned in this article

Here the JSON is stored in the same fashion as needed by you