Search code examples
azureazure-data-factory

Load Odata data to SQL Server

I am trying to load the data from Odata endpoint to SQL server but it is inserting only one record as the mapping takes first value. How can I set generic mapping/ flattern the data?

enter image description here

Sample Api response can be found here

{
    "@odata.context": "https://odata-dummy.com/odata/$metadata#Objectives",
    "value": [
        {
            "Id": 18118,
            "OwnerId": 2,
            "Description": "Quality: Provide the right care at the right time"
        },
        {
            "Id": 18115,
            "OwnerId": 3,
            "Description": "Acquisition: Provide the easiest path to care"
        },
        {
            "Id": 18116,
            "OwnerId": 5,
            "Description": "Result: Provide the easiest path to care"
        },
        {
            "Id": 18112,
            "OwnerId": 8,
            "Description": "Demo: Provide the easiest path to care"
        }
    ]
}

I want to extract value and insert in SQL server.

Solution

  • it is inserting only one record as the mapping takes first value.

    When your input contains array that you need to insert you have to unfold it you need to pass array in collection reference so it will unfold it and insert all the rows in SQL Table

    Use mapping like below image:

    enter image description here