ScheduleTrigger Without Overlap


  • I have an ADF v2 pipeline that does an incremental data copy of a table every hour to a different db.   The steps are as follows:

    1. truncate table1_stage on destination db
    2. copy data from source table1 to destination table1_stage
    3. merge records from table1_stage to table1 on destination

    This all works great until there are is a big batch of updates and the pipeline takes more than an hour, to complete.  A new trigger starts and truncates table1_stage while the previous trigger is still on step 3.  This obviously causes a problem.  

    To remedy I changed to a tumbling window with a max concurrency of 1.  This solves the major problem but introduced another annoyance.  If a pipeline takes 7 hours to run, when it finishes the tumbling window catches up and basically runs 7 times in a row to fill in the missed intervals.  Since I am not actually making use of the time windows in the job, this isn't really necessary, I would prefer it to just run an hour after the last finish time.  Seems like this would be easy with a similar max concurrency option on the scheduled trigger, but that doesn't appear to be available. Another option is to just set up a scheduled azure function or webjob to kick off the pipeline runs when I want them, but was hoping to keep all the operational aspects contained within ADF.  Is there any oter way to accomplish this?

    Sunday, May 20, 2018 6:21 PM

All replies

  • You could do the following:

    - Create a table in the destination database with 1 column called "inprogress" containing a boolean.

    - Create 2 stored procedures, 1 to update the inprogress row to true and 1 to update to false. 

    - On start of the scheduled trigger create a until script that contains a lookup if the boolean is true or false, don't forget to add a wait, otherwise it keeps doing it.

    - If its false, run the SP to update the inprogress to true. 

    - Let it run, when its done, run a storedprocedure to update it to false. 

    Wednesday, January 16, 2019 12:29 PM