ADF Copy Activity, Using parameter for query

Question

Hi All,

I am using "copy activity" to connect to Hive and bring data into SQL MI, for the copy activity I am using "use query" as "query", please see picture 1.
In the query I want to use where clause with a parameter driven value like today date. query will be something like this below

select * from dbo.src_location where businesseffectivedate>=convert(date,GETDATE())
and businesseffectivedate<=convert(date,GETDATE())

I am unable to figure out how to use filter criteria. Can you please help?
I know with the script transformation we can use parameters but in this case its a copy activity.

Picture 1:

Answer 1

Hi @ADFARI23 ,

Thanks for posting your query on Microsoft Q&A platform.

In copy activity too, you can use query with filter like below.

-using a plain query using getdate() in the where clause,

select * from dbo.src_location where businesseffectivedate>=GETDATE()

• if your where condition column comes from a pipeline parameter called 'dt', use below

@markus.bohland@hotmail.de ('select * from dbo.src_location where businesseffectivedate>=''',pipeline().parameters.dt,'''')

Same way you can use the filter condition with variables and functions.

Please let us know if you are stuck with syntax for your case.

Thanks.

Answer 2

@Subashri Vasudevan Thank you