Packvane

Translating SQL Agent Schedules into Data Factory Triggers

The schedule is not the hard part. The hard part is that a SQL Agent job is three things at once — a schedule, an ordered list of steps, and an error policy — and Data Factory splits those across a trigger, a pipeline, and activity dependencies. Translate them one at a time and the estate comes across cleanly. Translate the schedule alone and you will find out at 02:00 which assumptions the old jobs were carrying.

The three trigger types

Schedule trigger. Wall-clock recurrence: every day at 01:30, every 15 minutes, the third Tuesday. This is the direct equivalent of a SQL Agent schedule and it covers most jobs. One difference matters: a schedule trigger has no concept of a run that was supposed to happen while the factory was stopped. If the trigger is disabled at 01:30, the 01:30 run does not happen later — it does not happen.

Tumbling window trigger. Fires for a contiguous, non-overlapping window and carries that window's start and end as parameters. Use it for anything incremental: "load yesterday's rows" stops being a GETDATE()-1 expression inside the package and becomes a parameter the trigger supplies. Two properties have no SQL Agent equivalent and are the main reason to prefer it:

  • it backfills — start it with a date in the past and it runs every window since, in order;
  • it has dependencies — one window can be made to wait for another pipeline's window, which is how you express "the fact load waits for the dimension load" without a controller pipeline.

Storage event trigger. Fires when a blob arrives. Replaces the Foreach Loop over a landing folder that today runs every ten minutes and usually finds nothing. Files that show up at unpredictable times are the clearest win in the whole translation.

Job steps are not triggers

A five-step SQL Agent job is one pipeline with five activities, chained with Succeeded dependencies. It is not five pipelines and five triggers, and building it that way is the most common mistake in a half-finished migration: the schedule is preserved, the ordering is not, and two steps that used to be sequential now overlap.

While you are in there, read the on-failure action of each step. SQL Agent lets a step say "on failure, go to step 4". That is a Failed dependency in ADF, and it is genuine logic — usually a compensating action or a cleanup — that is invisible if you only look at the schedule.

Overlap: the behaviour change nobody plans for

SQL Agent will not start a job that is already running. The run is skipped and you get a message in the job history that nobody reads.

Data Factory schedule triggers have no such interlock. If the 01:00 run is still going at 01:15, the 01:15 run starts anyway, and two copies of a load that was never designed to be concurrent are now writing to the same tables.

This is the single most common production surprise in an SSIS-to-ADF migration, and it is invisible in testing because test data is small. Three ways out, in order of preference:

  1. Use a tumbling window trigger, which will not run two windows of the same pipeline concurrently, with maxConcurrency set to 1.
  2. Take an application lock at the top of the pipeline — sp_getapplock in a Script activity — and exit cleanly if it is already held.
  3. Keep a run-state row in a control table and check it. Cruder, but it works with any trigger type and it is visible to the people debugging at 02:00.

Before you choose, work out which jobs actually overlap today. The answer is in msdb.dbo.sysjobhistory: look for runs whose duration exceeds the schedule interval. Usually it is two or three jobs, and they are the important ones.

The jobs that are really dependencies

A good fraction of any SQL Agent estate is schedule-as-dependency: the extract runs at 01:00, the transform at 02:00, and the load at 03:00, not because those are meaningful times but because someone once measured the extract at forty minutes and left an hour of headroom. The data has grown since. The headroom has not.

Those three jobs are one pipeline. Making them one pipeline removes an entire class of nightly failure, shortens the batch window, and is the clearest argument you will have for doing the migration at all. It is also a behaviour change, so it belongs in the plan explicitly rather than as a quiet improvement.

A translation checklist

For each SQL Agent job, before you write any ADF:

  • schedule, including the enabled flag and whether it is actually used;
  • step list, with each step's on-success and on-failure action;
  • proxy account and credential per step;
  • longest observed run time from sysjobhistory, next to the schedule interval;
  • notifications: who is on the operator, and does anyone still read it;
  • whether the next job down the chain is waiting on a clock or on this job.

That table is the deliverable. Everything after it is mechanical.

We build it as part of an estate assessment — including the jobs that have been failing quietly since before anyone currently on the team joined. Tell us what you are running and we will tell you what is actually in there.