Passing Variables to Query Inside Lookup Activity in ADF

Question

I am trying to create an incremental Load Pipeline based on Pipeline Endtime. I have a source table Salesroifiles where i have file path, Filename, Sinkcreatedon and SinkModifieddon. I need to pick files after the last pipeline execution EndTime and current time.

Since i have a log table where i capture pipeline StartTime and endtime. I am using a lookup activity to get Max(Endtime) of pipeline and i m storing this value in a variable LastRunTime. i.e LastRunTime = FormatformatDateTime(activity('Lookup1').output.firstRow.LastRunEndTime, 'yyyy-MM-ddTHH:mm:ss')

Similarly I have created a variable CurrentTime = formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:ss') . Now i have another lookup activity where i have a Query as below

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')}'
  )

I am getting error at SQL Query in Lookup activity. Failure happened on 'Source' side.

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.
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.
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.
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.
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.',Source=,''Type=System.Data.SqlClient.SqlException,Message=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.
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.
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.
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.
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.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'Lookup1'.,},{Class=15,Number=132,State=1,Message=The label 'ddTHH' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'mm' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'ddTHH' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'mm' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'ddTHH' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'mm' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'ddTHH' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'mm' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'ddTHH' has already been declared. Label names must be unique within a query batch or stored procedure.,},{Class=15,Number=132,State=1,Message=The label 'mm' has already been declared. Label names must be unique within a query batch or stored procedure.,},],

Replaced direct variable embedding in Lookup SQL query with ADF expression using @concat() to prevent syntax issues from date format tokens. Constructed the full SQL dynamically using variables('LastRunTime') and variables('CurrentTime') to ensure safe and correct injection. — Balaji
– Balaji, Commented Jun 10 at 12:32

Pratik Lad · Accepted Answer · 2025-06-11 14:37:26Z

The error you're encountering is due to how dynamic content expressions are being interpreted and injected into the SQL query in your Azure Data Factory (ADF) pipeline.

Use ADF dynamic query composition with expression-based string construction, instead of hardcoding the query string directly. To resolve this follow below steps:

Go to your Lookup activity where the query is set.
Use the dynamic content editor (click the "Add dynamic content" link under the SQL query field).
Replace your SQL query with an expression like this:

@concat(
  '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'), ''')'
)

This uses the @concat() function to properly construct the SQL string, inserting the dynamic time values correctly.

Hijesh V L · Accepted Answer · 2025-06-10 14:29:50Z

0

From your question, I am not sure where your files are saved. In ADF if you want to pick up files after the last execution time, you can use Filter by last modified option in copy activity. You can pass the dynamic content to Start time (UTC) and End time (UTC) from your log table.

answered Jun 10 at 14:29

Hijesh V L

2792 silver badges11 bronze badges

Collectives™ on Stack Overflow

Passing Variables to Query Inside Lookup Activity in ADF

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related