question

NareshE-6629 avatar image
0 Votes"
NareshE-6629 asked MartinJaffer-MSFT commented

Trigger ADF Pipeline on second Friday from end of month and on Tues, Wed and Thurs of the same week.

Trigger ADF Pipeline on second Friday from end of month and on Tues, Wed and Thurs of the same week.
I need a Pipeline to trigger on the Monthend week (Tues, Wed, Thurs and Friday) of a month.

Monthend is defined as, "Last but one" Friday Or Second Friday from the end of the month.

For Example, For month of June 2021, 18th is the Monthend (Orange color as shown in the image).
91110-image.png

If its just on Monthend i.e. Second Friday from the end of calendar month, its easy. Just use Occurrance as -2 and day as Friday in the Scheduled trigger and add to a pipeline to trigger,

"schedule": {
"monthlyOccurrences": [
{
"day": "Friday",
"occurrence": -2
}
]
}
but I also need to run on the Tues, Wed and Thurs of the same week, which I find it difficult as these weekdays can be second or third from the end of the calendar month. For example: For June 2021, as shown in the image, I also need to run on 15th (Third Tuesday from the end of calendar month), 16th (Third Wednesday from the end of calendar month), 17th (Second Thursday from the end of calendar month).

Can you let me know if this can be implemented using triggers of Azure data factory? If not, any other ways of implementing?
Thank You!


azure-data-factory
image.png (246.0 KiB)
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.

NareshE-6629 avatar image
0 Votes"
NareshE-6629 answered MartinJaffer-MSFT commented

Thank you @MartinJaffer-MSFT for taking time and replying in a detailed way, learnt few things from it. Very encouraging for a person who just started with Azure Data Factory. I think, not only in ADF but other integration tools also it is not that straight forward.

Approach I am planning to use is very similar to yours:

--> Create a trigger which runs every Tues, Wed, Thur and Friday of a week, and add to this special_pipeline
--> Leverage the existing table which has month_start and month_end for each month.
--> In Special_pipeline, Lookup and get run_date and current_date using below query,


SELECT CASE WHEN DATENAME(WEEKDAY, GETDATE()) = 'Tuesday' THEN monthend-3
WHEN DATENAME(WEEKDAY, GETDATE()) = 'Wednesday' THEN monthend-2
WHEN DATENAME(WEEKDAY, GETDATE()) = 'Thursday' THEN monthend-1
WHEN DATENAME(WEEKDAY, GETDATE()) = 'Friday' THEN monthend
END as run_date,
GETDATE() curr_date
FROM dbo.special_calendar
WHERE getdate() BETWEEN monthstart AND monthend;

--> Add a IF condition to check if both dates are equal then execute business pipeline else do nothing.

Let me know if you see any issues with this.



· 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.

@NareshE-6629 I think that looks good. My colleagues agree. This looks easier to do in SQL, but I didn't suggest it because I didn't know you were using SQL.

Thank you for the positive feedback.

Once your issue has been resolved, please mark the appropriate post as accepted answer.

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered MartinJaffer-MSFT commented

Hello @NareshE-6629 and welcome to Microsoft Q&A.

I think this particular case is too much for a trigger alone to handle.
There are a few potential work-arounds, which fall into two categories:

Both of these will take some brainpower to implement. If you already have some scheduling tool you used for this scenario before, the first option may be easier.

Splitting the scheduling logic means accepting that the scheduled trigger will trigger on more days than you want, and building logic in the pipeline to short-circuit on the unwanted days. This way, even if the pipeline starts, nothing bad happens.

I am currently working on what expressions could be useful here.

One more option just occurred to me, but may be more than you need. Another pipeline which runs every month, to edit the trigger of the other pipeline. A trigger-creation pipeline.

· 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.

@NareshE-6629 I think I got some pipeline logic to roughly accomplish this. See attached file. You can copy-paste contents into a new data factory pipeline (except pipeline name). This should at least serve as code to learn from.

The attached pipeline assumes your scheduled trigger has the time-of-day aspect correctly taken care of. The pipeline tries to tell you whether the current day is inclusively between the last Friday of the current month and X days before then, where X is set in the parameters.

If you wanted to use this, you should follow the last activity with an if condition, inside of which place an execute pipeline activity. This way the "business" pipeline gets run only when this one approves.

91491-schedulelogiclastfullweek.txt


1 Vote 1 ·