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

Question

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

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!

Answer 1

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.

Answer 2

Hello @Naresh E 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:

• Use outside (not ADF) service to handle the schedule part, and tell ADF when to start a pipeline run
• Split the scheduling logic between a trigger and in-pipeline activities

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.