Forum Discussion

Shayan12378's avatar
Shayan12378
Regular Visitor
5 months ago

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

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 a...
data engineering
data pipeline
Srisakthi's avatar
Srisakthi
Icon for Super User rankSuper User
5 months ago

Hi Shayan12378 ,

 

Can you try along with Collection reference  ?

 

Thanks,

Srisakthi

  • Shayan12378's avatar
    Shayan12378
    Regular Visitor
    5 months ago

    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's avatar
      Srisakthi
      Icon for Super User rankSuper User
      5 months ago

      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

      • Shayan12378's avatar
        Shayan12378
        Regular Visitor
        5 months ago

        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