topic Re: Best way to set monthly job for data pipeline in Fabric in Pipelines

Best way to set monthly job for data pipeline in Fabric

shingot — Tue, 22 Aug 2023 05:32:41 GMT

I am making a data pipeline that should run once a month to aggregate monthly data but schedule interval can be set to one of minutes, hourly, daily and weekly. How can we setup monthly job?

Re: Best way to set monthly job for data pipeline in Fabric

GraceGu — Tue, 22 Aug 2023 05:44:14 GMT

Monthly scheduler is not supported yet. It is in plan.

Re: Best way to set monthly job for data pipeline in Fabric

shingot — Tue, 22 Aug 2023 14:10:40 GMT

Thank you for the answer. I hope the monthly option will be available soon. For now, we might try to use minutes scheduler as a workaround e.g. 44640 (31 days, it's OK for now as our project is at PoC phase) but is there maximum limitation?

Re: Best way to set monthly job for data pipeline in Fabric

rickmarques — Mon, 11 Sep 2023 10:03:28 GMT

Hey shingot check if this could work for you (it was the solution i used to do a end of month trigger)

This assumes you want to do it on end of month

CODE:

declare @current_date datetime = GETDATE()

CONVERT(varchar(10),@current_date, 120) = EOMONTH(getdate())

SELECT (either do your statement here or use a return value to pass it as a variable for the pipeline)

The pipeline executes everyday and does this validation first. If the current date is not the last day of the month, the pipeline will end there, if it's the last day of the month it'll execute the remaining pipeline (i send either a 0 or 1 as return and according to the returned value it either executes the rest of the pipeline or it stops there)

I think it's a more robust solution, since scheduling X amount of minutes will vary according to the number of days per month.

You can save the SQL query as a store procedure on datawarehouse, for instance, and invoke it on the pipeline.

Hope it helps.

Best regards,

Ricardo Marques