Event-Driven SQL Server Queue Processing

React to new rows in a SQL Server table the moment they appear, instead of running a batch job on a cron schedule that is either too slow for busy periods or wasteful during quiet ones. This blueprint polls a queue-style table every minute, and only starts an execution when rows are actually waiting. It solves the classic polling problem of a fixed-interval batch job: pick an interval too short and you waste connections on empty polls, pick it too long and pending work sits idle.

How it works

1. The pending_rows trigger (io.kestra.plugin.jdbc.sqlserver.Trigger) polls dbo.order_queue every minute for rows where status = 'NEW'.
2. afterSQL runs in the same transaction as the polling query, flipping matched rows to PROCESSED immediately. This means a row is claimed the instant it is read, so a slow-running execution or an overlapping poll never processes the same row twice.
3. The flow only starts when the query returns at least one row (fetchType: FETCH), so quiet periods create zero executions instead of a stream of empty runs.
4. process_row (io.kestra.plugin.core.flow.ForEach) iterates trigger.rows, running the handle task once per pending row so a slow item does not block the rest of the batch.
5. notify posts the row count to Slack; the errors block alerts separately if processing fails partway through.

What you get

• Work starts within a minute of appearing, without a human tuning a cron interval.
• Rows are claimed atomically (afterSQL in the same transaction), so overlapping polls or slow executions never double-process a row.
• Zero executions during quiet periods, since the trigger only fires when rows exist.
• Per-row isolation via ForEach, so one bad row does not stall the rest of the batch.

Who it's for

• Application teams using SQL Server as a lightweight outbox or work queue.
• Platform teams replacing a SQL Server Agent polling job with event-driven execution.
• Teams who need at-least-once row processing without standing up a message broker.

Why orchestrate this with Kestra

SQL Server Agent can run a job on a schedule, but it has no native concept of "only run when there is work," and coordinating a claim-then-process pattern safely requires hand-written transaction logic in every consumer. Kestra's JDBC trigger polls and claims rows atomically via afterSQL, only starts an execution when there is real work, and gives every batch a full execution history with per-row visibility through ForEach, without deploying Kafka Connect or a separate queue.

Prerequisites

• A SQL Server database with a queue-style table (dbo.order_queue in this example) that has a status column.
• A Slack incoming webhook for notifications.

Secrets

• SQLSERVER_URL: JDBC URL, for example jdbc:sqlserver://your-host:1433;trustServerCertificate=true.
• SQLSERVER_USERNAME / SQLSERVER_PASSWORD: database credentials.
• SLACK_WEBHOOK_URL: Slack incoming webhook URL.

Quick start

1. Add the secrets above to your Kestra namespace.
2. Point sql and afterSQL at your own queue table and status column names.
3. Replace the handle task with the real per-row action your queue exists to trigger.
4. Deploy the flow; it starts polling immediately on the PT1M interval.

How to extend

• Add an errorMessage per-row check inside ForEach to route bad rows to a dead-letter table instead of failing the whole batch.
• Swap the Log task for an HTTP call, a file export, or a chain into another orchestration page's pattern, like Slack or Snowflake.
• Add conditions on the trigger to skip polling outside business hours if the queue is only ever populated during the day.
• Tighten or loosen the PT1M interval based on how latency-sensitive the downstream consumers are.

Links

• Plugin docs: https://kestra.io/plugins/plugin-jdbc-sqlserver
• SQL Server orchestration with Kestra: https://kestra.io/orchestration/sql-server