Lookup activity in an Azure Data Factory Data Copy Source - Syntax Error

Question

Working through this https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-portal

I've one Lookup activity called GetCurrentWatermarkValue with sqlReaderQuery:

Select WaterMarkValue as CurrentWatermarkValue\nfrom WatermarkTable

I've another activity called GetNewWatermarkValue with sqlReaderQuery:

select max(createdon) as NewWatermarkValue from shipment

I'm then trying to use them both in the source for a Data Copy activity using

select *
from Shipment
where CreatedOn > '@{activity('GetCurrentWatermarkValue').output.firstRow.CurrentWatermarkValue}' 
and CreatedOn <= '@{activity('GetNewWatermarkValue').output.firstRow.NewWatermarkValue}'

The Preview data button is greyed (but enabled when i remove the where condition) so there is obviouusly something wrong

After I've set the sink i try to set up the Mapping. Clicking Import schema on the Mapping tab gives:

A database operation failed with the following error: 'Incorrect syntax near 'GetCurrentWatermarkValue'.'. Activity ID:98794aa9-c866-48d6-b9ff-9cb277bac6ed

I thought maybe I should be using the add dynamic content option but that just gives

Query is required

I've read somewhere that the when First Row Only is set on the Lookup activity the text after firstRow. should be [TableName] but that doesn't seem right.

Lookup:

{
    "name": "GetCurrentWatermarkValue",
    "type": "Lookup",
    "policy": ...,
    "typeProperties": {
        "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "Select WaterMarkValue as CurrentWatermarkValue\nfrom WatermarkTable"
        },
        "dataset": {
            "referenceName": "WatermarkTable",
            "type": "DatasetReference"
        }
    }
}

Lookup:

{
    "name": "GetNewWatermarkValue",
    "type": "Lookup",
    "policy": ...,
    "typeProperties": {
        "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "select max(createdon) as NewWatermarkValue from shipment"
        },
        "dataset": {
            "referenceName": "ShipmentsTable",
            "type": "DatasetReference"
        }
    }
}

Data Copy:

{
    "name": "ArchiveShipments",
    "type": "Copy",
    "dependsOn": [
        {
            "activity": "GetCurrentWatermarkValue",
            "dependencyConditions": [
                "Succeeded"
            ]
        },
        {
            "activity": "GetNewWatermarkValue",
            "dependencyConditions": [
                "Succeeded"
            ]
        }
    ],
    "policy": ...,
    "typeProperties": {
        "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": {
                "value": "select *\nfrom Shipment\nwhere CreatedOn > '@{activity('GetCurrentWatermarkValue').output.firstRow.CurrentWatermarkValue}' \nand CreatedOn <= '@{activity('GetNewWatermarkValue').output.firstRow.NewWatermarkValue}'",
                "type": "Expression"
            }
        },
        "sink": {
            "type": "AzureSqlSink"
        },
        "enableStaging": false
    },
    "inputs": [
        {
            "referenceName": "ShipmentsTable",
            "type": "DatasetReference"
        }
    ],
    "outputs": [
        {
            "referenceName": "ShipmentArchiveTable",
            "type": "DatasetReference"
        }
    ]
}

Martin Esteban Zurita Martin Esteban Zurita · Accepted Answer · 2019-05-30T19:38:20

This looks like a syntax issue, try this is the copy activity query:

@{CONCAT('select * from Shipment where CreatedOn > ', activity('GetCurrentWatermarkValue').output.firstRow.CurrentWatermarkValue, ' and CreatedOn <= ', activity('GetNewWatermarkValue').output.firstRow.NewWatermarkValue}

Dont use a mapping unless you want to manually set it, if column names are the same, then just click "Clear".

Hope this helped!

Lookup activity in an Azure Data Factory Data Copy Source - Syntax Error

1 Answers