ADF Copy Data, Sink all JSON Object into SQL Cell

Question

I am using copy data activity in Azure data factory (ADF) to make calls to 5 different APIs in the loop and all 5 APIs will have 5 different responses.

The idea is to sink the whole JSON Object into the SQL database. I want to know how can I refer to all API JSON object responses.

For example, here is my API Response:

 {
     result:[{
    "elemenName":"Element Name",
     "elemenName":"Element Name",

   }]
   }

I do not want to sink the result in the database cell, but instead, I want to be able to sink the whole JSON object in the cell. I am not sure how to refer to this JSON object. my API JSON response is greater than 5MB

Answer 1

• In for-each activity, you can use lookup activity to read the json API data and then use the Script actvity to insert the json data that is read from lookup activity into the SQL table. Below is the approach.

• In Lookup activity, select HTTP as linked service and json as source dataset.

• Enter the Base URL and in Relative URL, enter the value from for-each item as dynamic content.

• Then add a script activity and add the linked service for SQL database in it.

• Enter the query as a dynamic content in query text box.

Insert into <sink-table-name> values ('@{activity('Lookup2').output.value}')

• When pipeline is run, json data from each api is copied to table as separate rows.

Update:

Message=The size of lookup activity result exceeds the limitation`

To resolve this, Inside for-each, use the copy activity and take the source dataset as delimited text with different column and row delimiter. In this case, I took (``) as both column and row delimiter, so that the entire Json response is in a single row.

Copy the data to sink database.