pass parameters to SQL query in Azure data factory

Question

How can we pass the parameter to SQL query in Azure Data Factory?

EG: Select * from xyz_tbl where date between @date1 and @date2

If I use stored procedure with output @date1 and @date2 parameter, how can I pass these parameters to SQL query?

Answer 1

If you are using Azure Data Factory V2, you can make this easily by using “Lookup activity” that queries the database to get your dynamic properties and then referencing the output of the activity in your SQL query in the following mentioned way.

select * from xyz_tbl 

where date between @{activity('LookupActivity').output.date1} 

and @{activity('LookupActivity').output.date2}

For more clarification regarding “Lookup activity” in Azure Data Factory, refer to this documentation.

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity