Azure Data Factory V2 Using Params in SQL

Question

New to ADF in general and hitting a speed bump with the following scenario, would really appreciate any guidance on how to achieve this.

In my pipeline I want to use simple SQL to control how much data I process, so using:

SELECT * FROM DPA.USER_REGISTRATIONS 
WHERE REG_DATE BETWEEN DATE '2020-01-01' AND DATE '2020-06-09'

Using the 'preview data' button/tool in ADF UI this returns a sample of exactly what I am looking for. Great, but I need the second DATE value to always be the current date. I'm sure this must be very simple but I can't seem to find the right way to form the params/expressions to get it to work.

I am trying:

SELECT * FROM DPA.USER_REGISTRATIONS 
WHERE REG_DATE BETWEEN DATE '2020-01-01' AND DATE @pipeline.parameters.today;

Where I have a pipeline level parameter defined with name: today and value: utcnow() But that doesn't work.

Can someone please advise on how to create and reference a suitable param? Thanks!

what about creating a stored procedure and move this query to it and also use GetDATE() rather than receiving the date as a parameter? — Thiago Custodio
– Thiago Custodio, Commented Jun 10, 2020 at 13:32

Joel Cochran · Accepted Answer · 2020-06-10 13:52:36Z

0

To do this based on dynamic values, you will need to use the Pipeline Expression Language (PEL) (and most likely variables) to build the query string. This answer may prove useful to you.

answered Jun 10, 2020 at 13:52

Joel Cochran

7,7732 gold badges35 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

Azure Data Factory V2 Using Params in SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related