pass parameters to SQL query in Azure data factory

azure azure-data-factory
Jack picture Jack · Nov 14, 2017 · Viewed 9.6k times · Source

How can we pass parameter to SQL query in Azure data factory ...

ex: 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 parameter to sql query.

Answer

Antonia picture Antonia · Nov 23, 2017

Which version of Azure DataFactory are you using, v1 ir v2?

If v2 is an option for you, you could achieve this by using Lookup activity that queries the database to get your dynamic properties and then referencing that activity's output in your sql query like so:

select * from from xyz_tbl 
where date between @{activity('LookupActivity').output.date1} 
and @{activity('LookupActivity').output.date2}

Here's a more detailed msdn tutorial for the Lookup activity: https://docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

Related questions

The client with object id does not have authorization to perform action 'Microsoft.DataFactory/datafactories/datapipelines/read' over scope

I was trying to invoke data factory pipeline from azure function programmatically. Its throwing following error. link: http://eatcodelive.com/2016/02/24/starting-an-azure-data-factory-pipeline-from-c-net/ AuthorizationFailed: The client 'XXXX-XXXXX-XXXX' with object id 'XXX829e05'XXXX-XXXXX' does not have authorization to perform action 'Microsoft.DataFactory/…

Read More
The subscription is not registered to use namespace 'Microsoft.DataFactory error

Going through this tutorial "Create a pipeline with Copy Activity using Visual Studio" and recieving this error when i hit publish. Error not mentioned anywhere on net and very little help/knowledge on azure generally on web.

Read More
Using Azure Data Factory to get data from a REST API

Is it possible to use Azure Data Factory to get data from a REST API and insert it to a Azure database table?

Read More