pass parameters to SQL query in Azure data factory

Question

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.

Antonia · Accepted Answer · 2017-11-23 17:18:23Z

16

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://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

answered Nov 23, 2017 at 17:18

Antonia

2883 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SNR Over a year ago

Just in case someone is working with a query like where DATETIME_MODIF >= '@{activity('MAX_DT_MODIF').Output.firstRow.MAXDT}' I had to add single quotes. Thanks for the info Antonia!

Brandon Over a year ago

Unfortunately, this runs dynamic sql instead of using sql parameter markers - any thoughts on how to get it to use parameter markers?

Eric Over a year ago

using the @{activity...} syntax is far better than Microsoft and other recommendations of using @concat(...) - especially when trying to insert dynamic (incremental load) dates into a large SQL script. Plus, it avoids the need to find and replace all single quotes with triple quotes that are requied with @concat()

Collectives™ on Stack Overflow

pass parameters to SQL query in Azure data factory

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related