question

sivasudarsan-2412 avatar image
0 Votes"
sivasudarsan-2412 asked NandanHegde-7720 commented

Azure data factory- pipeline trigger with date input parameters


Adf pipeline query db(copy activity) , create/write to a file.
Adf copy pipeline souce dataset has query where it should take dynamic parameters as input(date). Ex: select startdate,enddate,count(*) from table where startdate>=? And enddate<=?
Second req: Append these dates to the file created as header.

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered NandanHegde-7720 commented

Hey @sivasudarsan-2412 ,
PFB the responses:
1) Any reason why you want to create parameters at dataset level?
You can create a pipeline parameter of type: String and leverage it in your copy activity dynamic query
wherein you can format it into ant time format supported by ADF as per your need for comparison
2) There are 2 aspects:
In case if the pipeline is scheduled one, then whatever parameter value is present in ADF pipeline, it would take it as the default value while execution.
But in case of any manual trigger, you can override the parameter value in case if you want to pass a different value.
You can also leverage Variables in case if you want to update the values intermittently within Pipeline executions
3) Can you be little clear on this part ???????
4) Parameter names can be anything : Test, Testing etc.
There is no restriction on the parameter name being same as the columnnames

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1) clear. I don't need data set param.
2) my pipeline is scheduled nightly but it should take two input dynamic parameters. Ex: If pipeline is scheduled for today night 00:01;00 then first date param is yesterday date 00:00:00 and second param is after 24 hrs of first param. So these changes daily.
How to incorporate dynamic values in scheduled.
3) My db table has single date column which is used as both start date and end date in query.
How to map a single column as two parameters?

0 Votes 0 ·

Hey
2)Rather than having a parameter, you can create variables and assign the variable values at run time :
there are multiple datetime functions to provide you the values like:
@pipeline().TriggerTime or UTCnow() etc and based on these values you can assign the variables values

3) you mean to say a single column in source is being populated for 2 columns in sink?
If that is the case:
in source have a select query wherein you create alias for the same column:
Select c1 as x, c1 as y from table1

1 Vote 1 ·
NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered sivasudarsan-2412 edited

Hey.
In Source dataset: make a dynamic query as below:

select startdate,enddate,count(*) from table where startdate>='@{pipeline().parameters.Filter}'


where Filter is the parameter name

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1) Can i create the date parameters in source datasets parameters with type string and default value as YYYY-MM-DD hh:mm:ss[.nnnnnnn] ? because my database table has value 2020-11-11 23:08:44.8000000.
2) Also how the pipeline expects these parameters before starting/triggering?
My req is - in devops pipeline i have powershell/cli task to start this pipeline with two parametrs as start date and end date. Then adf pipeline is triggered with those two dates and these dates values should go into the query as params.
3)Also how can i manage if my both input params (start date and endDate) both refer to same column in database table?
because i get validation error in pipeline saying these params are duplicated.
4)Also do i need to gave param names exactly same as table column name?

0 Votes 0 ·