topic Re: I wanted to Store Rest API Response as A JSON in the SQL Database Table in Data Engineering

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

Shayan12378 — Thu, 12 Mar 2026 11:52:22 GMT

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

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

Srisakthi — Thu, 12 Mar 2026 13:48:23 GMT

Hi @Shayan12378 ,

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

Thanks,

Srisakthi

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

Shayan12378 — Thu, 12 Mar 2026 13:50:29 GMT

Yes

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

Srisakthi — Thu, 12 Mar 2026 13:54:04 GMT

Hi @Shayan12378 ,

Can you try along with Collection reference ?

Thanks,

Srisakthi

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

ShayanSiddiqueI — Thu, 12 Mar 2026 14:12:35 GMT

Could you please explain what to do in mapping

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

Shayan12378 — Thu, 12 Mar 2026 14:16:32 GMT

@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

}

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

Srisakthi — Thu, 12 Mar 2026 14:57:08 GMT

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-synapse-analytics-sql-pool/

Regards,

Srisakthi

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

Shayan12378 — Fri, 13 Mar 2026 11:14:53 GMT

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

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

Srisakthi — Fri, 13 Mar 2026 15:18:52 GMT

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-ver17

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

Regards,

Srisakthi

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

ShayanSiddiqueI — Fri, 13 Mar 2026 15:19:31 GMT

Sure

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

v-priyankata — Mon, 16 Mar 2026 12:22:23 GMT

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.