ADF中Lookup活动传递变量至SQL查询时报错的问题

问题场景 #

基于Pipeline EndTime创建增量加载管道，需从源表Salesroifiles筛选出上次管道执行结束时间到当前时间范围内的文件。具体实现：

• 通过Lookup活动从日志表获取管道Max(Endtime)，存入变量LastRunTime，表达式：

  LastRunTime = formatDateTime(activity('Lookup1').output.firstRow.LastRunEndTime, 'yyyy-MM-ddTHH:mm:ss')
  

• 创建变量CurrentTime：

  CurrentTime = formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:ss')
  

• 在另一个Lookup活动中执行SQL查询：

  SELECT 
      REPLACE(ImsDownload, 'https://**.blob.core.windows.net/attachments/', '') AS ImsDownload
  FROM MSI.tblSalesDocuments
  WHERE ImsAttachmentType = 7
    AND ImsDownload IS NOT NULL
    AND ImsDownload NOT LIKE '%sharepoint%'
    AND (
          ModifiedOn BETWEEN '@{variables('LastRunTime')}' AND '@{variables('CurrentTime')}'
       OR SinkCreatedOn BETWEEN '@{variables('LastRunTime')}' AND '@{variables('CurrentTime')}'
       OR SinkModifiedOn BETWEEN '@{variables('LastRunTime')}' AND '@{variables('CurrentTime')}'
    )
  

错误信息 #

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'Lookup1'.
The label 'ddTHH' has already been declared. Label names must be unique within a query batch or stored procedure.
The label 'mm' has already been declared. Label names must be unique within a query batch or stored procedure.
...（重复错误信息省略）

错误原因 #

1. 格式字符串被误识别为SQL标签：变量中formatDateTime的格式yyyy-MM-ddTHH:mm:ss包含ddTHH、mm，SQL引擎将其误判为GOTO语句的标签（格式为label:），重复出现的标签触发“已声明”错误。
2. 变量解析失败：Incorrect syntax near 'Lookup1'说明LastRunTime的表达式未正确执行，原始表达式片段直接代入SQL，引发语法错误。

解决方法 #

方法1：使用参数化查询（推荐） #

避免直接拼接变量，通过Lookup活动的参数传递时间值，自动处理类型转换与转义：

1. 修改SQL为带参数的形式：

   SELECT 
       REPLACE(ImsDownload, 'https://**.blob.core.windows.net/attachments/', '') AS ImsDownload
   FROM MSI.tblSalesDocuments
   WHERE ImsAttachmentType = 7
     AND ImsDownload IS NOT NULL
     AND ImsDownload NOT LIKE '%sharepoint%'
     AND (
           ModifiedOn BETWEEN @LastRunTime AND @CurrentTime
        OR SinkCreatedOn BETWEEN @LastRunTime AND @CurrentTime
        OR SinkModifiedOn BETWEEN @LastRunTime AND @CurrentTime
     )
   

2. 在Lookup活动的「参数」选项卡添加：
   • 参数名LastRunTime，值为variables('LastRunTime')
   • 参数名CurrentTime，值为variables('CurrentTime')

方法2：修正变量格式与SQL拼接 #

若坚持直接拼接，需确保时间值被正确转义，避免格式字符串触发SQL标签识别：

1. 调整变量表达式，提前用单引号包裹时间值，并替换T为空格：
   • LastRunTime表达式：

     concat("'", formatDateTime(activity('Lookup1').output.firstRow.LastRunEndTime, 'yyyy-MM-dd HH:mm:ss'), "'")
     

   • CurrentTime表达式：

     concat("'", formatDateTime(utcNow(), 'yyyy-MM-dd HH:mm:ss'), "'")
     

2. 修改SQL，去掉变量外的单引号：

   SELECT 
       REPLACE(ImsDownload, 'https://**.blob.core.windows.net/attachments/', '') AS ImsDownload
   FROM MSI.tblSalesDocuments
   WHERE ImsAttachmentType = 7
     AND ImsDownload IS NOT NULL
     AND ImsDownload NOT LIKE '%sharepoint%'
     AND (
           ModifiedOn BETWEEN @{variables('LastRunTime')} AND @{variables('CurrentTime')}
        OR SinkCreatedOn BETWEEN @{variables('LastRunTime')} AND @{variables('CurrentTime')}
        OR SinkModifiedOn BETWEEN @{variables('LastRunTime')} AND @{variables('CurrentTime')}
     )
   

方法3：验证变量有效性 #

通过Set Variable活动或Debug模式，确认LastRunTime和CurrentTime的实际值为合法时间字符串，同时检查activity('Lookup1')返回的LastRunEndTime是否为有效日期类型，避免空值或非法格式导致的解析失败。

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