Dynamically update filed value in ADF Copy activity

Question

I am trying to perform copy activity in ADF. Source for the pipeline is API(Json response) and Sink is Azure Sql data base.

I want to check, if the enddat is null then only i have take name field and map to sink column. Kindly note that i am using copy activity not data flow.

Response from API is in below format:

[
{
        "id": "8d6c",
        "customsiteid": "wew",
        "country": "US",
        "state": "CA",
        "city": "San Jose",
        "sampleIds": [
            {
                "name": "klio",
                "acceptedat": "2016-02-02T00:00:00.000Z",
                "enddat": null
            },
            {
                "name": "jkdsjks",
                "acceptedat": "2011-12-23T00:00:00.000Z",
                "enddat": "2016-01-26T00:00:00.000Z"
            }
        ]
    },
    {
        "id": "kjkj",
        "customsiteid": "iuewi",
        "country": "UK",
        "state": "Na",
        "city": "Mnv",
        "sampleIds": [
            {
                "name": "asa",
                "acceptedat": "2017-02-02T00:00:00.000Z",
                "enddat": "2019-01-26T00:00:00.000Zx"
            },
            {
                "name": "wer",
                "acceptedat": "2021-12-23T00:00:00.000Z",
                "enddat": null
            }
        ]
    }
    ]

Please let me know the approach to achieve this scenario.

Answer 1

You can achieve this using 2 copy activities instead of one. The first one to stage the entire data in your SQL database and the second one to query only the records where enddat is null.

• In the first copy activity, I am writing to a demo table in my Azure SQL Database (sink). The following is the mapping that I have used.

• The data will be successfully written and the result will be as shown below:

• In the second copy data activity, use the above table (demo) as source and then use the following query to get required source data.

select * from demo where enddt is null

• Now, select the sink table from your Azure SQL database and use the mapping as shown in the below image:

• This would give the desired result as shown in the below image: