Azure Data Factory动态查询传递日期数组变量时类型转换失败

作为Azure Data Factory（ADF）新手，我正在构建从源表到目标表的数据复制管道，要求每次仅写入一个快照日期的数据。流程如下：

• 通过Lookup活动获取目标表最新的snapshot_date
• 再用Lookup活动获取源表中需复制的snapshot_date数组并存储到变量
• 通过ForEach活动循环变量进行数据复制

但执行时出现错误：Conversion failed when converting date and/or time from character string

当前变量的JSON结果：

{
    "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"
        }
    ]
}

复制活动的JSON配置：

{
    "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
        }
    }
}

目前传递给查询的是带结构的字符串'{\"snapshot_date\":\"2023-10-11T00:00:00\"}'，请问如何正确将数组变量结果传递到该查询中？

解决方案 #

问题根源是ForEach循环中传递了完整的JSON对象而非单独的日期值，导致SQL查询接收到非法的字符串格式，引发类型转换错误。按以下步骤修正：

1. 配置ForEach活动的迭代项
   确保ForEach活动的Items属性直接引用数组变量：@variables('var_source_array_of_dates')，每次循环会迭代数组中的单个对象元素。

2. 提取循环项中的日期值到查询
   修改复制活动的sqlReaderQuery，使用ADF表达式提取当前循环项的snapshot_date字段值：

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

   若目标SQL DW的日期类型需要特定格式，可通过formatDateTime函数统一转换：

   select ID, snapshot_date from source_table where snapshot_date = '@{formatDateTime(item().snapshot_date, 'yyyy-MM-ddTHH:mm:ss')}'
   

   上述表达式会直接提取纯日期字符串，而非整个JSON结构。

3. 修正Sink类型错误
   当前配置中Sink类型为SqlDWSource，这是错误的，需改为SqlDWSink才能正常写入数据：

   "sink": {
       "type": "SqlDWSink",
       "writeBehavior": "Insert",
       "sqlWriterUseTableLock": false
   }
   

内容的提问来源于stack exchange，提问作者SusanD