I wanted to Store Rest API Response as A JSON in the SQL Database Table

[Shayan12378]

I am using Copy Activity in Azure Data Factory in which I am using REST API for getting the Response from the API I am getting an array contain multiple dictionaries. I wanted to Store the Response as a Raw Format in the SQL Table but In the Mapping  I am getting the Individual  to be connected with a single Table How Can I store the Response of the API in a Single Row as a JSON Format

Above is attached JSON Prasing I am getting. 
@azuredatafactory @copyactivity

[ShayanSiddiqueI]

Could you please explain what to do in mapping  

[Srisakthi]

Hi @Shayan12378 ,

Have you tried enabling the checkbox "Map complex values to string"

Thanks,

Srisakthi

[Shayan12378]

Yes

[Srisakthi]

Hi @Shayan12378 ,

Can you try along with Collection reference  ?

Thanks,

Srisakthi

[Shayan12378]

@Srisakthi  With the Below  Mapping

It is not writting any data in my SQL Table Field

{

"dataRead": 833639,

"dataWritten": 0,

"sourcePeakConnections": 1,

"sinkPeakConnections": 1,

"rowsRead": 60,

"rowsCopied": 142,

"copyDuration": 21,

"throughput": 166.728,

"errors": [],

"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",

"usedDataIntegrationUnits": 4,

"billingReference": {

"activityType": "DataMovement",

"billableDuration": [

{

"meterType": "AzureIR",

"duration": 0.016666666666666666,

"unit": "DIUHours"

}

],

"totalBillableDuration": [

{

"meterType": "AzureIR",

"duration": 0.016666666666666666,

"unit": "DIUHours"

}

]

},

"usedParallelCopies": 1,

"executionDetails": [

{

"source": {

"type": "RestService"

},

"sink": {

"type": "AzureSqlDatabase",

"region": "East US"

},

"status": "Succeeded",

"start": "3/12/2026, 7:14:16 PM",

"duration": 21,

"usedDataIntegrationUnits": 4,

"usedParallelCopies": 1,

"profile": {

"queue": {

"status": "Completed",

"duration": 12

},

"transfer": {

"status": "Completed",

"duration": 5,

"details": {

"readingFromSource": {

"type": "RestService",

"workingDuration": 1,

"timeToFirstByte": 0

},

"writingToSink": {

"type": "AzureSqlDatabase",

"workingDuration": 0

}

}

}

},

"detailedDurations": {

"queuingDuration": 12,

"timeToFirstByte": 0,

"transferDuration": 5

}

}

],

"dataConsistencyVerification": {

"VerificationResult": "Unsupported"

},

"durationInQueue": {

"integrationRuntimeQueue": 0

}

}

[Srisakthi]

Hi @Shayan12378 ,

Consider this example, I have my json like this format and i want to store orders entire json into one of my column in my sql db table.

under mapping section of ADF, click on import schemas, select "Map complex values as string" and then if you see orders json come as array and then select column name to store. Exclude other columns

sometime it takes time to refresh the arrays properly

You can also check this link for reference https://sarnendude.com/azure-data-factory-copy-activity-how-to-save-json-data-as-string-in-azure-syn...

Regards,

Srisakthi

[Shayan12378]

It is help me to store a part of my response. Like you are saving the Order part from the whole response but I wanted to save whole response in a Single Column

[Srisakthi]

Hi @Shayan12378 ,

I have tried with data flow as well but no luck. Other option is store the API response in json file(ADLS) and use OPENROWSET BULK

https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-bulk-transact-sql?view=sql-server-v...

Also you can raise support ticket with Microsoft , may be they can give direction on achieving this scenario.

Regards,

Srisakthi

[v-priyankata]

Hi @Shayan12378 

Thank you for reaching out to the Microsoft Fabric Forum Community.
@Srisakthi Thanks for the inputs.
@Shayan12378 As suggested by Super User, have you raised a support ticket? If so, please share your details here. If not, please raise one here. Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn.

If you have any other issues, please reach community.
Thanks.

[ShayanSiddiqueI]

Sure