Search code examples
jsonazurerestazure-data-factory

Extract Data from API in ADF

I need to extract data from REST API in ADF, which I am doing using WEB activity and COPY activity.

When used Web activity, I am getting extra \ in response as below.

{
    "Response": "[{\"Name\":\"Countries\",\"Url\":\"/api/Datab/abc\",\"Trees\":[{\"Name\":\"Economics Locations\"},{\"Name\":\"Economics Indicators\"}]},{\"Name\":\"Global\",\"Url\":\"/api/Datab/def\",\"DatabankCode\":\"WDMacro\",\"StartYear\":1980,\"EndYear\":2050,\"HasQuarterlyData\":true,\"Trees\":[{\"Name\":\"Global Economics Locations\"},{\"Name\":\"Global Economics Indicators\"}]}]",
    "ADFWebActivityResponseHeaders": {
        "Pragma": "no-cache",
        "Rate-Limit-60": "Unthrottled (0/60 requests) in 60 sec window",
        "Rate-Limit-60-Status": "Unthrottled",
        "Rate-Limit-60-Window": "60",
        "Rate-Limit-60-RequestCount": "0",
        "Rate-Limit-60-RequestLimit": "60",
        "Cache-Control": "no-cache",
        "Date": "Mon, 20 Feb 2023 11:32:52 GMT",
        "Server": "Microsoft-IIS/10.0",
        "X-AspNet-Version": "4.0.30319",
        "X-Powered-By": "ASP.NET",
        "Content-Length": "17782",
        "Content-Type": "application/json; charset=utf-8",
        "Expires": "-1"
    },
    "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (West Europe)",
    "executionDuration": 0,
    "durationInQueue": {
        "integrationRuntimeQueue": 0
    },
    "billingReference": {
        "activityType": "ExternalActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ]
    }
}

When I Used COPY activities(created REST DS and Linked service), I got below issue, I am assuming this is because of \ . enter image description here

Can someone please help me,

  1. How to get rid of \ in response of WEB activity output?
  2. How to make COPY activity WORK, So I can sink to Storage account?
  3. I need to make another API call for each Url of Response? As in this output, How do I parse Url to capture /api/Datab/abc and /api/Datab/def
Solution

  • I tried to reproduce the similar issue and got similar response with extra slash(\)

    enter image description here

    Then I took set variable activity and created array variable with name demo and converted it into Json format.

    @json(activity('Web1').output.Response)

    enter image description here

    this will give you correct format of array.

    enter image description here

    Then pass this array variable to Foreach activity as below.

    enter image description here

    To access Url from this variable in foreach activity use @item().Url

    enter image description here

    OUTPUT

    enter image description here