Azure Data Factory: Passing array variable in dynamic query that filters Date column fails with data type conversion

Question

I'm new to ADF and I'm trying to build a pipeline that copies record from source table to target table. Source table:

ID	snapshot_date
2	2023-10-11
3	2023-10-12
4	2023-10-13

Target table:

ID	snapshot_date
1	2023-10-10

I want to only write records one snapshot at a time. So I added a Lookup that captures the most recent snapshot_date in the Target table. Then added a new Lookup activity to capture an array of all snapshot_date values from Source table that must be copied to the Target table. Passed the result of this Lookup activity to a Variable. Then connected this to a ForEach activity to loop through the variable and copy each snapshot to the Target table. But I'm receiving this error "Conversion failed when converting date and/or time from character string"

Here is the JSON file of the Variable result:

{
    "variableName": "var_source_array_of_dates",
    "value": [
        {
            "snapshot_date": "2023-10-11T00:00:00Z"
        },
        {
            "snapshot_date": "2023-10-12T00:00:00Z"
        },
        {
            "snapshot_date": "2023-10-13T00:00:00Z"
        }
    ]
}

And Here is the JSON file of the Copy activity:

{
    "source": {
        "type": "SqlDWSource",
        "sqlReaderQuery": "\nselect ID, snapshot_date from source_table where snapshot_date = '{\"snapshot_date\":\"2023-10-11T00:00:00\"}'",
        "queryTimeout": "02:00:00",
        "partitionOption": "None"
    },
    "sink": {
        "type": "SqlDWSource",
        "writeBehavior": "Insert",
        "sqlWriterUseTableLock": false
    },
    "enableStaging": false,
    "translator": {
        "type": "TabularTranslator",
        "typeConversion": true,
        "typeConversionSettings": {
            "allowDataTruncation": true,
            "treatBooleanAsNumber": false
        }
    }
}

Is it because it is passing this variable outcome to the query? '{\"snapshot_date\":\"2023-10-11T00:00:00\"}'"

Can anyone help me figure out how to pass the array variable result to this query?

Answer 1

The above error arises due to the query that you are using. Inside ForEach, you are using @{item()} in the query which is a JSON object in each iteration.

To overcome the error, you need to give the snapshot_date of each item in the query which is something like this.

select ID, snapshot_date from source_table where snapshot_date = '@{item().snapshot_date}';

For sample, I have taken a lookup activity instead of copy activity and a similar query to yours. Your query needs to be like this.