Data Factory V2 Query Azure Table Storage but use a lookup Value

Question

I have a SQL watermark table which contains the last date in my destination table

My source data is coming from an Azure Storage Table and the date time is a string

I set up the date time in the watermark table to match the format in the Azure table storage

I create a lookup and a copy task

If I hard code the date into the Query for source and run this works fine CreatedAt ge '2019-03-06T14:03:11.000Z'

But obviously I dont want to hard code this value. I want to use the date from the lookup

But when I replace the hardcoded date with the lookup value

CreatedAt ge 'activity('LookupWatermarkOld').output'

I get an error

{
"errorCode": "2200",
"message":"ErrorCode=FailedStorageOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A storage operation failed with the following error 'The remote server returned an error: (400) Bad Request.'.,Source=, ''Type=Microsoft.WindowsAzure.Storage.StorageException,Message=The remote server returned an error: (400) Bad Request., Source=Microsoft.WindowsAzure.Storage,StorageExtendedMessage=Syntax error at position 42 in 'CreatedAt ge 'activity('LookupWatermarkOld').output''.\nRequestId:8c65ced9-b002-0051-79d9-d41d49000000\nTime:2019-03-07T11:35:39.0640233Z,,''Type=System.Net.WebException,Message=The remote server returned an error: (400) Bad Request.,Source=Microsoft.WindowsAzure.Storage,'",
"failureType": "UserError",
"target": "CopyMentions"
}

Can anyone help me with this? How do you use the Lookup value in a Azure Table query?

Answer 1

check this out:

1) Lookup activity. Query field:

SELECT MAX(WatermarkColumnName) as LastId FROM TableName;

Also, make sure that you checked "First row only" option.

2) In Copy Data activity use query. Query field:

@concat('SELECT * FROM TableName as s WHERE s.WatermarkColumnName > ''', activity('LookupActivity').output.firstRow.LastID, '''')

Answer 2

Finally I got some help on this and it works with

CreatedAt gt '@{activity('LookupWatermarkOld').output.firstRow.WaterMarkValue}'

the WaterarkValue is the column name from the SQL Lookup table

The Lookup creates an array so you have to specify the FirstRow from this array

And wrap in '' so its used as a string value

Answer 3

--For recent ADFv2

Use the watermark/lookup/output value in parameter.

Example: ParamUserCount = @{activity('LookupActivity').output.count}

or for output function

and you can use it in query as

Example: "select * from userDetails where usercount = {$ParamUserCount}"

make sure you enclose the query in " " to set as string and parameter in query should be enclosed in { }