如何编写含SQL查询的多条件ADF动态过滤表达式

问题场景 #

我正尝试为Azure Data Factory（ADF）的Filter活动编写动态内容，需对Get Metadata活动获取的、文件名含日期的文件进行多条件过滤：

• 文件扩展名必须为.csv或.zip；
• 文件名中的日期需大于等于SQL数据库控制表（dbo.ADF_Control_Table）中对应文件的last_success_run_date，实现FTP源的增量加载。

编写的动态表达式报错，无法正常运行，尝试的表达式如下：

@and(or(endswith(item().name,'.zip'),endswith(item().name,'.csv')),
greaterOrEquals(formatDateTime(substring(item().name,6,10),'yyyy-MM-dd'),
formatDateTime(
select last_success_run_Date from dbo.ADF_Control_Table 
where FileName = '@{substring(replace(item().name,".zip",""),17, sub(length(item().name), 21))}',
'yyyy-MM-dd'
)))

核心问题 #

ADF的动态表达式无法直接嵌入并执行SQL语句，你当前的写法违反了ADF的表达式规则，这是报错的根本原因。必须先通过Lookup活动预获取控制表数据，再在Filter中引用匹配。

修正步骤 #

1. 新增Lookup活动获取控制表数据 #

创建一个Lookup活动（命名为Lookup_Control_Table），连接目标SQL数据库，配置查询语句：

SELECT FileName, last_success_run_Date FROM dbo.ADF_Control_Table

该活动会返回所有文件的控制记录，供后续Filter活动匹配使用。

2. 修改Filter活动的动态表达式 #

使用Lookup活动的输出数据，匹配当前文件对应的last_success_run_date后再做日期对比，修正后的表达式如下：

@and(
    or(endswith(item().name, '.zip'), endswith(item().name, '.csv')),
    greaterOrEquals(
        formatDateTime(substring(item().name, 6, 10), 'yyyy-MM-dd'),
        formatDateTime(
            first(filter(activity('Lookup_Control_Table').output.value, 
            equals(item().FileName, substring(replace(item().name, '.zip', ''), 17, sub(length(item().name), 21)))).last_success_run_Date,
            'yyyy-MM-dd'
        )
    )
)

关键说明：

• activity('Lookup_Control_Table').output.value：Lookup活动返回的控制表数据集合；
• filter函数：根据文件名匹配控制表中对应的记录；
• first函数：提取匹配到的第一条记录的last_success_run_Date；
• 确保文件名截取逻辑（substring(replace(item().name, '.zip', ''),17, sub(length(item().name), 21))）能精准匹配控制表的FileName字段值。

3. 空值防护优化 #

若控制表中可能存在无匹配的文件，建议添加空值处理避免报错，比如用coalesce设置默认日期（示例默认取1900-01-01，可按需调整）：

@and(
    or(endswith(item().name, '.zip'), endswith(item().name, '.csv')),
    greaterOrEquals(
        formatDateTime(substring(item().name, 6, 10), 'yyyy-MM-dd'),
        formatDateTime(
            coalesce(first(filter(activity('Lookup_Control_Table').output.value, 
            equals(item().FileName, substring(replace(item().name, '.zip', ''),17, sub(length(item().name), 21)))).last_success_run_Date,
            '1900-01-01'),
            'yyyy-MM-dd'
        )
    )
)

4. 额外验证点 #

确认substring(item().name,6,10)能准确提取文件名中的日期字符串，且格式符合yyyy-MM-dd，否则formatDateTime会转换失败。

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