Automatically creating Jira tickets for failed SQL Server Agent jobs

When a SQL Server Agent job fails, the usual process is manual: someone notices, opens job history, copies the error into a Jira ticket and chases the right team. Each step adds delay. This guide outlines how to automate it — detect the failure, capture diagnostics and raise a Jira ticket — and the design decisions that keep it from creating noise.

1. Choose how failures are detected

There are two common patterns:

• On-failure step. Add a final step to each job and set the other steps' On failure action to “Go to step N”. Simple, but it has to be added to every job and is easy to forget.
• Central poller. A single scheduled job checks msdb.dbo.sysjobhistory and SSISDB.catalog.executions for new failures every few minutes. One place to maintain, and it covers jobs nobody remembered to configure.

For more than a handful of jobs, the central poller is usually the better choice. Keep a small table of processed failures so each one is handled exactly once.

2. Capture the diagnostics that matter

A useful ticket answers the first questions an engineer will ask, without opening SSMS:

• Job or package name, failing step or component, and server
• Start time, duration, and the error message itself
• Row counts for the run compared with a normal run
• What the job feeds downstream — which datasets and reports are affected

For SSIS catalog executions, error text comes from SSISDB.catalog.event_messages with message_type = 120; for Agent jobs, from the message column of the failing step in sysjobhistory.

3. Create the Jira issue

Jira Cloud's REST API creates issues with a POST to /rest/api/2/issue, authenticated with an Atlassian account email and API token. A minimal PowerShell example, suitable for a SQL Agent PowerShell step or a scheduled script:

$jira  = "https://your-site.atlassian.net"
$pair  = "$($env:JIRA_USER):$($env:JIRA_API_TOKEN)"
$auth  = [Convert]::ToBase64String([Text.Encoding]::UTF8.GetBytes($pair))

$body = @{
  fields = @{
    project     = @{ key = "DATA" }
    issuetype   = @{ name = "Bug" }
    priority    = @{ name = "High" }
    summary     = "SSIS_Stg_CRM_Account failed - PK violation on stg.Account"
    description = "Server: SQLPROD01`nStep: Load Account`nError: Violation of PRIMARY KEY constraint..."
    labels      = @("etl-failure", "auto-created")
  }
} | ConvertTo-Json -Depth 5

$issue = Invoke-RestMethod -Method Post -Uri "$jira/rest/api/2/issue" `
  -Headers @{ Authorization = "Basic $auth" } `
  -ContentType "application/json" -Body $body

$issue.key   # e.g. DATA-1287

Attach longer diagnostics — the full execution log, row counts, the downstream impact list — with a POST to /rest/api/2/issue/{key}/attachments, sending the file as multipart form data with the header X-Atlassian-Token: no-check.

4. Avoid duplicate tickets

A job that fails every 15 minutes should not create 30 tickets by morning. Before creating an issue, search for an open one for the same job (for example by a label or a custom field holding the job name) using Jira's JQL search API, and add a comment to it instead. Check Atlassian's current documentation for the search endpoint, as it has been revised in recent API versions.

5. Route to the team that owns the data

Map jobs to owning teams (a simple table of job name → Jira project, component and assignee is enough to start) so tickets arrive with the people who can fix them. If the failure affects business-critical reports, raise the priority and notify the report owners too.

6. Keep credentials out of job steps

Never hard-code the API token in a job step. Use a SQL Agent proxy with a credential, environment variables for the service account, or a secrets store — and scope the Atlassian account to the projects it needs.

Where this leads

Built well, this turns a failed job into an actionable ticket within seconds of the failure. Built quickly, it turns into alert noise. The difference is deduplication, useful diagnostics and correct routing.

That is exactly what Automated Incident Management does for SQL Agent and SSIS: failure detection, execution analysis, error capture, a Jira ticket with diagnostics attached and notification of the responsible team.