Staging Scheduler

The Staging Scheduler automates batch processing so external systems can push data into staging tables without manual intervention. Each entity is configured independently with its own processing mode, schedule, and triggers.

The scheduler section appears on the Staging Config tab under Processing Schedule. Use the toggle to enable or disable the scheduler without losing the configuration.

Processing modes

Three modes are available. Select one by clicking its card in the Processing Schedule section.

Manual

The default mode. A user clicks Process Batch on the Staging Data tab. No automation — every batch is user-initiated.

Scheduled

Batches run automatically on a fixed schedule. Four schedule types are available:

The panel shows a live Next run preview based on the current settings.

How to configure a daily schedule:

1. Select Scheduled mode
2. Choose Daily from the schedule type dropdown
3. Type a time (e.g. 06:00) in the time input and click Add
4. Add additional times as needed — each appears as a chip you can remove
5. Click Save Schedule

Weekly and Monthly work the same way, but add day selection (checkboxes for weekdays, or a 1–31 grid for month days).

Trigger-Based

Trigger-Based mode with row threshold, idle timeout, and new rows detection(click to enlarge)

Batches fire automatically when data conditions are met. Three trigger types can be combined:

Trigger priority: When multiple triggers are configured, they are evaluated in this order:

1. Row threshold — checked first. If pending rows >= threshold, fire immediately
2. Idle timeout — checked second. If any row has waited longer than the timeout, fire
3. New rows detected — checked last. If rows exist and the debounce window has elapsed, fire

The first condition that matches wins. The others are not evaluated.

Composite triggers: Set both a row threshold and an idle timeout to create a composite trigger. Example: "Process when 500 rows queue up, or when any row has been waiting 15 minutes — whichever comes first." This balances throughput (larger batches are more efficient) against latency (rows should not age indefinitely).

How "new rows detected" works

The dispatcher runs on a 60-second polling loop. Each cycle, it counts the rows with ImportStatus = 0 in the staging table. If the count is greater than zero and the debounce window has elapsed since the last check, the trigger fires.

Debounce window: A configurable delay (default: 60 seconds) that prevents thrashing. When an ETL tool inserts 10,000 rows one at a time, the debounce window ensures the scheduler collects them into one batch rather than firing on every individual insert.

Effective latency: Because the dispatcher polls every 60 seconds, the actual delay from row insertion to batch start is:

debounce window ± 60 seconds

With the default 60-second debounce, worst case is approximately 120 seconds. If your integration requires sub-second processing, call usp_Staging_ProcessBatch directly from your ETL pipeline instead of using the scheduler.

Overlap guard

If a batch is still running when the next scheduled or triggered run would fire, the new run is skipped — not queued. The skip is recorded in the Scheduler Log.

This prevents double-processing and eliminates the risk of concurrent batch runs causing deadlocks.

Zombie timeout: If a batch has been running for more than 30 minutes without completing, the overlap guard treats it as stuck and releases the lock. The release is logged so administrators can investigate.

Zombie timeout

The scheduler runs every 60 seconds and checks all entities with an active schedule. To prevent overlapping runs, each entity has an overlap guard: while a batch is running (IsRunning = 1), the scheduler skips that entity.

If a batch is still marked as running after the zombie timeout, the scheduler assumes it is stuck ("zombie") and forcibly releases the lock. It then re-evaluates the schedule immediately — if the schedule conditions are met, a new batch starts right away.

Configure the zombie timeout in Staging Config → Processing Schedule → Advanced options.

Status bar

When the mode is Scheduled or Trigger-Based and a config has been saved, a status bar appears showing:

• Current status — Idle (grey) or Running with a pulsing indicator and elapsed time
• Next run — the calculated next execution time (Scheduled mode only)
• Last run — relative time since the last completed batch
• Pending rows — number of rows currently waiting with ImportStatus = 0

Scheduler log

The Scheduler Log section (collapsed by default, below the Save button) records every scheduler event:

Each entry shows: timestamp, event type, trigger source, message, queue depth at the time, and batch duration.

The log is paginated (10 entries per page) and only visible when the mode is not Manual.

SQL Agent setup

The scheduler requires a single SQL Agent job that calls the dispatcher stored procedure. All scheduling logic lives in the stored procedure — the job itself is a one-liner.

Job step command:

EXEC dbo.usp_Scheduler_Dispatch;

Recommended job settings:

• Name: Primentra_Staging_Dispatcher
• Schedule: Every 1 minute, 24/7
• On failure: Do not retry — the next 60-second tick handles recovery

One job serves all entities. The dispatcher checks every entity with scheduling enabled and fires batches where conditions are met.

Alternatives to SQL Agent

If SQL Agent is unavailable (SQL Server Express, Azure SQL), call the stored procedure from any external scheduler:

Windows Task Scheduler:

"C:\Program Files\Microsoft SQL Server\...\SQLCMD.EXE" -S 127.0.0.1 -d Primentra -E -Q "EXEC dbo.usp_Scheduler_Dispatch"

Linux cron:

* * * * * /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -d Primentra -U sa -P 'password' -Q "EXEC dbo.usp_Scheduler_Dispatch"

The stored procedure is the contract — any mechanism that calls it on a 60-second interval works.

Batch history — trigger source

When scheduling is active, each batch in Batch History shows a Trigger Source indicating what started the run: