scheduled trigger should be disabled automatically once activity is done

Question

Hi ,
I have ADF pipeline inside that I used stored procedure activity and call stored proc. inside SP I checked DTU percentage , if DTU percentage is less than 5% then create index.
That pipeline is scheduled every 15 min but once my DTU condition is meet at first time then don't execute pipeline for that day and for next day again it will check DTU condition.

so my question is that can I disable scheduled trigger automatically once my condition is meet for that day. I don't want to run this pipeline for entire day or 24* 7?
and it will be enabled for next day automatically or can I write any such SQL code to achieve such task?
TIA

Answer 1

Hello @Girish13 and thank you for your question.
This ask reminds me of a related Q&A thread where the ask was for a pipeline to stop its own trigger if the pipeline failed.
While the ask here is different, I believe similar methods could apply.

Unlike the other thread, here you still need a way to turn the trigger back on at the start of each day.

@Nasreen Akter is correct there is no built-in feature for dynamically changing the trigger state. This could be because the trigger state is included in the definitions stored in Git Integration or Azure Dev-Ops Integration. Changes to the trigger state in the Data Factory Service would become inconsistent with that stored in the source control.
This said, it is possible to call the Data Factory API and change the trigger state. This will cause inconsistency with source control.

While I think a work-around using only Data Factory is possible, @Jaryd makes a good point that using another service such as Logic Apps may be easier, and possibly more reliable than using only Data Factory. Reliable in the sense that tying trigger state to the success of every other activity in the pipeline. Suppose the stored proc fails. Depending upon implementation, this could prevent the call to change trigger state.

If you desire to do this entirely in Data Factory, then you will need at least 2 pipelines and 2 triggers.

One pipeline (let's name it "Starter") has the role of turning on the trigger for the other pipeline. We need a scheduled trigger to run "Starter" once every day. All "Starter" does is use a Web activity to turn on the other trigger with this rest api.

The other pipeline (let's name it "DoData") contains the Stored Proc activity and other logic mentioned in the ask. In order to know whether it is time to turn off the trigger, we need to get feedback from that stored procedure. In your database, alter the stored procedure to return a value telling us whether the DTU condition is met or not.
In the pipeline swap out the Stored Proc activity for a Lookup Activity. The Stored Proc activity does not expect data to be returned, but the Lookup Activity requires data to be returned. The Lookup Activity can use stored procedure.
Feed the output of the Lookup Activity to an If-Condition activity. The If-Condition should use the value returned by the Lookup Activity's stored procedure to determine whether or not to use a Web activity to call the Data Factory API to disable to trigger.

Details for how to use the web activity to call the api can be found in the thread I mentioned earlier.

Answer 2

Hi @Girish13 ,

This is an interesting question. My understanding is that there is no way in the DataFactory you can automatically disable/enable scheduled trigger.

What you can do:

• you can save the decision in SQL database i.e., disable from and to datetime, dtu% etc. and then add checking at the beginning of the pipeline (i.e., main pipeline which scheduled in every 15 mins) and according to the checking you can procced or skip the following SP --> pipelines.

----------

please don't forget to Accept as answer and up-vote, if the reply is helpful! Thanks!

Answer 3

I think you could handle this in logic apps.

Unfortunately I don’t know the best logic to use to handle your situation, but when logic gets too complicated for data factory, you can do so in logic apps.

I would set a scheduled time trigger for the logic app to run every 15 minutes. Same as the other comment, you would save the decision. But for some reason, I feel much more comfortable a logic app doing a check every 15 minutes over an ADF pipeline.