Trigger Azure Data Factory Pipeline on second Friday from end of month and on Tues, Wed and Thurs of the same week

Question

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) Calendar 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 otherways of implementing? Thank You!

Answer 1

The scheduled trigger alone is not capable of that logic (as of 2021-05-04). Easiest solution would be to use some other scheduling application.

For a purely Data-Factory solution, schedule the trigger for all the days the desired days could possibly occur on. Then modify the pipeline to do logic to determine whether the current day is actually one of the desired days.

Implementation details and sample code

The logic:

1. Find the last day of the month (First of the next month less 1 day).
2. Subtract a week so you are in the second-to-last week
3. Loop over [0,-1,-2,-3,-4,-5,-6] as number of days to add to the date. This produces the dates of each day of the week.
4. Use the dayOfWeek function to change the date into which day of the week it is
5. Filter to get the Friday date
6. Ask whether today is between the Friday date and Friday date - 3 days