Hi @Svgk, Raju (Nokia - IN/Chennai) ,
Welcome to Microsoft Q&A platform and thanks for posting your question.
As I understand your requirement, you want to load the data of nested JSON into two tables in SQL DB. Please let me know if my understanding is incorrect.
You need to use Mapping Data flow to achieve this .
1. Create the dataset pointing to the JSON file and select 'Array of documents' in JSON settings under Source option.
2. Create New branch from the source transformation and add flatten activity in each branch
3. For stores table, use 'Stores' array to unrollby and select the name column .
4. For Items table, use 'Stores.Items' array to unrollby and select name and price columns
5. In the sink transformation , create dataset pointing to stores and items table respectively and disable auto-mapping . While mapping manually, don't map identity columns in stores and items table. Also, one point to be taken care is money datatype for Item_Price in sink table is not compatible in mapping.
Once the data gets copied into the two tables, you can create triggers inside SQL to update Item_Store_ID from Items table according to the corresponding Store_ID in Stores table.
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