To copy data from single payload to multiple tables.

Question

I have a JSON payload containing student information stored in an Azure Blob container. The JSON includes details like StudentName, age, address, course, semester, universityName, universityAddress, and universityRating.

I need to transfer this data from the JSON file in the Blob container to an SQL database, with the following mapping:

Name, age, and address should go into the studentInfo table.

course and semester should be placed into the courseInfo table.

universityName, universityAddress, and universityRating should be mapped to the universityInfo table.

Can you explain the steps to achieve this in Azure Data Factory with Data Flows?

JSON Payload :

[

{

"Name": "John Doe",

"Age": 21,

"Address": "123 Elm Street, Springfield, IL",

"Course": "Computer Science",

"Semester": 5,

"University": {

  "UniversityName": "Springfield University",

  "UniversityAddress": "456 University Ave, Springfield, IL",

  "UniversityRating": 4.5

}

},

{

"Name": "Jane Smith",

"Age": 22,

"Address": "789 Oak Street, Springfield, IL",

"Course": "Information Technology",

"Semester": 6,

"University": {

  "UniversityName": "Springfield University",

  "UniversityAddress": "456 University Ave, Springfield, IL",

  "UniversityRating": 4.5

}

},

{

"Name": "Alice Johnson",

"Age": 20,

"Address": "321 Pine Street, Springfield, IL",

"Course": "Software Engineering",

"Semester": 4,

"University": {

  "UniversityName": "Springfield University",

  "UniversityAddress": "456 University Ave, Springfield, IL",

  "UniversityRating": 4.5

}

}

]

Accepted Answer

Hello Ashish Adhikari,

Welcome to the Microsoft Q&A forum.

You can follow the below steps to transfer the data from the JSON file in the Blob container to an SQL database with the given mapping in Azure Data Factory with Data Flows:

Create a new pipeline in Azure Data Factory and add a new Data Flow activity to it.

In the Data Flow, add a Source transformation and configure it to read the JSON file from the Blob container.

Add a Flatten transformation (since your JSON has nested structure) after the Source transformation and select the "University" field. Choose the "Flatten by Name" option to break out the nested fields into separate columns.

Add a Derived Column transformation and create new columns for the fields that need to be mapped to the studentInfo, courseInfo, and universityInfo tables. For example, create a new column called "StudentName" and set its value to the "Name" field from the JSON payload.

Add a Sink transformation for each of the tables (studentInfo, courseInfo, and universityInfo) and configure them to write the data to the corresponding tables in the SQL database. Map the columns from the Derived Column transformation to the appropriate columns in the tables.

Connect the transformations in the Data Flow

Save and publish the pipeline.

I hope this helps. Please let me know if you have any further questions.

Share via

To copy data from single payload to multiple tables.

0 additional answers

Your answer