Scheduled SQL Server Report Emailed as a CSV Attachment

Turn a daily SQL Server reporting query into an automated email without writing glue code or babysitting a SQL Server Agent job step. This blueprint runs an aggregate query against SQL Server on a schedule, converts the result to CSV, and emails it to stakeholders as an attachment. It skips the send entirely when a day has no data and posts a Slack alert if anything fails, so a missed morning report is never silent. It also stays fully backfillable: rerun any past day on demand without editing the flow.

How it works

1. A io.kestra.plugin.core.trigger.Schedule trigger fires every morning at 06:00 UTC (cron: 0 6 * * *).
2. The daily_sales_report task (io.kestra.plugin.jdbc.sqlserver.Query) aggregates the report day's orders by region and stores the full result set in internal storage (fetchType: STORE). The day is bound as a :day prepared-statement parameter.
3. The email_when_data task (io.kestra.plugin.core.flow.If) checks outputs.daily_sales_report.size > 0.
4. When there is data, report_to_csv (io.kestra.plugin.serdes.csv.IonToCsv) converts the stored ION result to CSV and email_report (io.kestra.plugin.email.MailSend) emails it over SMTP with TLS as an attachment.
5. When there is no data, log_no_data (io.kestra.plugin.core.log.Log) records that the day was empty instead of sending an empty attachment.
6. If the run fails, an errors block sends a io.kestra.plugin.slack.notifications.SlackIncomingWebhook alert with the execution id.

The report day is derived from inputs.report_date (else the trigger or execution date minus one day) and rendered once into vars.report_day, which drives the SQL filter, the email subject, and the log line in lockstep.

What you get

• A daily sales report delivered straight to inboxes as a CSV attachment.
• No empty-attachment noise: the email is sent only when the day produced rows.
• Backfill any past day by running the flow with report_date set, no code changes.
• A Slack alert on failure, separate from the report email itself.

Who it's for

• Data engineers who need scheduled operational reporting off a SQL Server OLTP database.
• Analysts and ops teams who want a daily metrics email without a custom SSIS package.
• Platform teams replacing SQL Server Agent job steps plus Database Mail with a single declarative pipeline.

Why orchestrate this with Kestra

SQL Server Agent can run a stored procedure or a T-SQL job step on a schedule, but it cannot convert results to CSV, branch on whether the result was empty, attach a file to an email over authenticated SMTP, or alert Slack when the job fails without extra Database Mail configuration and custom T-SQL. Kestra ties all of that together in one declarative YAML flow: a time trigger starts the run, the errors block guarantees failures surface, and every execution is captured with full logs and lineage you can replay. Because the date is parameterized, the same flow that runs nightly also backfills history.

Prerequisites

• A reachable SQL Server instance with an dbo.orders table (created_at, region, total columns).
• An SMTP server reachable from the Kestra workers (this flow uses SMTP over TLS on port 587).

Secrets

Set these as Kestra secrets, never inline:

• SQLSERVER_URL: JDBC URL, for example jdbc:sqlserver://host:1433;trustServerCertificate=true.
• SQLSERVER_USERNAME / SQLSERVER_PASSWORD: SQL Server credentials.
• SMTP_HOST, SMTP_USERNAME, SMTP_PASSWORD: SMTP relay credentials.
• REPORT_FROM_ADDRESS: sender address for the report email.
• REPORT_RECIPIENTS: recipient email address(es) for the report.
• SLACK_WEBHOOK_URL: the Slack incoming webhook URL the failure alert is posted to.

Quick start

1. Create the secrets above.
2. Confirm dbo.orders has created_at, region, and total columns, or adjust the SQL.
3. Adjust the cron expression as needed.
4. Enable the flow. It runs on the schedule; to backfill a day, run it manually with report_date set.

How to extend

• Swap the SQL for any other SQL Server aggregate, or template the table and grouping columns through inputs.
• Change the cadence by editing the cron, or add a second schedule for a weekly rollup.
• For large result sets that a mail relay would reject, write the CSV to object storage and email a download link instead of an attachment.
• Add a Slack success notification alongside the email, or chain the CSV into Snowflake for a warehouse-side copy of the same report.

Links

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