sqlazure-data-factory

Passing Variables to Query Inside Lookup Activity in ADF

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.,},],
Solution

  • 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:

    1. Go to your Lookup activity where the query is set.

    2. Use the dynamic content editor (click the "Add dynamic content" link under the SQL query field).

    3. 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.