0

I have a copy activity that takes a bunch of JSON files and merges them into a singe JSON.

enter image description here

enter image description here

I would now like to copy the merged single JSON to Azure SQL DB. Is that possible?

Ok, it appears to be working however the output in SQL is just countryCode and CompanyId

enter image description here

However, I need to retrieve all the financial information in the JSON as well

enter image description here

Improve this question
8
  • do all the merged files have the same json format? If so you should be able to use a Copy Activity to do it. If they have different formats then I wouldn't merge them together. Commented Nov 30, 2022 at 16:31
  • Yes all the merge files have the same format. Do you have an example showing the solution? Commented Nov 30, 2022 at 16:35
  • Hi Scott, I'm not sure if you read my question fully... The files have already been merged. I would like to know if I can copy the 'merged' json into our SQL DB? Commented Nov 30, 2022 at 16:42
  • Just create a Copy Activity from the json file to Sql database then. If the format matches the db table then you don't need to specify the schema. Of the copy activity has an option to automatically create the table for you matching the json schema. Commented Nov 30, 2022 at 16:46
  • I chose the option to automatically create the table but I keep on getting the message 'Table is required for Copy Activity' Commented Nov 30, 2022 at 16:49

1 Answer 1

Reset to default
1

Azure Data Factory Copy Activity for JSON to Table in Azure SQL DB

I repro'd the same and below are the steps.

  • Two json files are taken as source. enter image description here

  • Those files are merged into single file using copy activity. enter image description here

enter image description here

  • Then Merged Json data is taken as source dataset in another copy activity.

enter image description here

  • In sink, dataset for Azure SQL db is created and Auto create table option is selected.

enter image description here

  • In sink dataset, edit checkbox is selected and sink table name is given. enter image description here

  • Once the pipeline is run, data is copied to table.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Aswin, this is amazing. Going to check this out now.
Hi Aswin, your solution appears to be working, but its not importing all the fields in the JSON
While copying the Merged JSON data to SQL Db using copy activity, In mapping tab , 1) Import Schema, 2) Toggle to the advanced editor. 3) Select the collection reference array value with which you want to iterate and extract data. 4) Give the names in the mapping for sink columns,if not available. Refer this MS doc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.