CODEWITHSUNDEEP
azureazure-pipelinesazure-data-factoryazure-data-explorer
Sankeertan Samrat
Sankeertan Samrat

Reputation: 195

Dynamic SQL to Load azure copy data activity

I am trying to create a dynamic query in copy data activity to extract data based on a timestamp from a table. The lookup also gets the right value

I have tried

  1. select * from [dbo].t_tablename where last_changed_date > @{formatDateTime(activity('Lookup_Get Last changed date').output.firstRow.lastRecordTimeStamp,'yyyy-MM-ddTHH:mm:ssZ')}

  2. select * from [dbo].t_tablename where last_changed_date > @{activity('Lookup_Get Last changed date').output.firstRow.lastRecordTimeStamp}

  3. select * from [dbo].t_tablename where last_changed_date > @activity('Lookup_Get Last changed date').output.firstRow.lastRecordTimeStamp

and other options but it always gives an ERROR 2200 which is a syntax error.

Can someone suggest what is the approach?

if i try this(It works) select * from [dbo].t_tablename where last_changed_date >'2019-11-27 16:17:00.000'

Upvotes: 1

Views: 3883

Answers (2)

Sankeertan Samrat
Sankeertan Samrat

Reputation: 195

select * from [dbo].t_tablename where last_changed_date > '@{activity('Lookup_Get Last changed date').output.firstRow.lastRecordTimeStamp}'

The above code is working now...as it is a datetime we have to include the activity in between ' '

Upvotes: 0

Joel Cochran
Joel Cochran

Reputation: 7748

Is the syntax error coming from SQL or from ADF?

Assuming it is SQL, when building the statement dynamically, you still need 's (quotes) around the where clause value:

select * from [dbo].t_tablename where last_changed_date > **'**@{formatDateTime(activity('Lookup_Get Last changed date').output.firstRow.lastRecordTimeStamp,'yyyy-MM-ddTHH:mm:ssZ')}**'**

If it is ADF, try building the value in a concat expression:

@concat('select * from [dbo].t_tablename where last_changed_date > ''', activity('Lookup_Get Last changed date').output.firstRow.lastRecordTimeStamp, '''')

In either case, you need quotes around the value.

Upvotes: 4

Related Questions