How to use variable in the query using Azure data factory?

Question

I'm trying to use the trigger_start_time variable in the query as the 2nd parameter to a function of lookup activity.

How can I do this?

Please use concat() to build the query. What's the whole query statements? Where did want to put the variable TriggerTime? — Leon Yue
– Leon Yue, Commented Mar 10, 2021 at 1:29
My query looks like this : select schema.fn_up_watermark('@{item().table_name}',variable(trigger_start_stime)) from dual — dev333
– dev333, Commented Mar 10, 2021 at 1:35
I have kept the variable trigger_start_time in set variable with value @pipeline.TriggerTime — dev333
– dev333, Commented Mar 10, 2021 at 1:36
Hi @dev333, Please try @concat('select schema.fn_up_watermark(',Parameter_name,',',variable_name, ') from dual') in add dynamic content . — Leon Yue
– Leon Yue, Commented Mar 10, 2021 at 1:46
Thanks a lot for ur help ... This is the query I've given .. it is throwing missing right paranthesis .. @concat('select schema. fn_up_watermark(',item().table_name,',',variables('trigger_start_time'), ') from dual') — dev333
– dev333, Commented Mar 10, 2021 at 2:28

Leon Yue · Accepted Answer · 2021-03-10 04:15:42Z

1

Please try @concat('select schema.fn_up_watermark(',Parameter_name,',',variable_name, ') from dual') in add dynamic content.

I just make this example with the query you provide me. You could test that not use the parameter and variable to check where missing the right parentheses.

Now, I'm glad to hear the issue is resolved now.

answered Mar 10, 2021 at 4:15

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

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

1 Comment

PhilHibbs Over a year ago

How do you protect against injection attacks? Does ADF have a quote_literal or quote_ident function to wrap values safely?

Jason Welch · Accepted Answer · 2021-03-13 15:20:19Z

0

You can do away with the variable and simply write:

select schema.fn_up_watermark('@{item().table_name}','@{pipeline().TriggerTime}') from dual

assuming both parameters are string.

You could still do SQL injection if you can affect the outer lookup that feeds this ForEach block - you could sanitise the table_names on entering this loop possibly.

answered Mar 13, 2021 at 15:20

Jason Welch

9516 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to use variable in the query using Azure data factory?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related