Automating SQL Server to Jira with Python (Phase 1)
I’ve been spending a lot of time thinking about observability and how I can use AI to automate fixing problems, not just finding them.
The goal is to build a system that can spot an issue, try to fix it, and only alert a human if it gets stuck. A self-healing system.
Last quarter in grad school, I took a course on solving real-world problems with Python, and I’ve been combining what I learned there with my past work experience. This is something I’m building at home in my free time using my own test servers with my own paid enterprise accounts.
But you can’t just jump into the AI part. You have to build the foundation first.
In this post, I’ll walk through Phase 1 of this project, which covers:
- Grabbing some information from SQL Server
- Setting up the configuration and project area in Jira
- Sending SQL Server information to Jira
This first part is all about getting non-sensitive data out of SQL Server and into Jira, where it can be tracked and worked on. Once it’s in Jira, it can be picked up by a person or, later, by an AI agent.
You can find the phase 1 full scripts here: https://github.com/aaa-dba/JiraAutomation. More scripts will be added as I continue blogging through this pet project of mine.
Using AI to Write Code
I actually tried using AI to build this process. In my experience, the AI overcomplicated the code and made it hard to maintain or understand.
If you are using AI to write your code, keep this in mind. It’s usually easier to write the base script yourself, then think about how you’ll train AI to use it later. Don’t expect the AI to get everything right the first time. It takes time to train it. I will explain this and show how this works with windsurf and other AI tech in another blog later.
What You’ll Need (Prerequisites)
Before you start, make sure you’re doing all of this on a test server.
It’s best to run and test everything in a separate project so your team doesn’t get spammed while you fine-tune the setup. Run everything at your own risk and take time to test the code thoroughly. Make sure you understand both the impact and the cost because nothing in the cloud is free.
SQL Server
Things to do for SQL Server
Step 1: Create SQL Server User
You’ll need a SQL USER with read access to the MSDB database on SQL Server. Depending on your specific tasks, you may require more or fewer permissions.
You’ll use this username and password later, so it’s best to store them securely in a vault rather than hardcoding them in your script.
For now, this is used in the script just for testing purposes.
Step 2: Create the Job Failures in SQL Server
You can create test SQL Agent jobs that fail on purpose so you have something to report on.
Here is a script that:
- Creates jobs
- Purposely makes the jobs fail
- Selects the jobs and returns the results
You can find this script in my GITHUB repo here:
https://github.com/aaa-dba/JiraAutomation/blob/main/CreateSQLJobsPurposelyFail.sql
This script will create a bunch of jobs, make them fail, then return the results.
Step 3: Install ODBC Driver for SQL Server
Download for Mac:
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql18Download for Windows:
- Download ODBC Driver
- Run this command to verify the installation:
powershell -NoLogo -NoProfile -Command "Get-OdbcDriver"You should see “ODBC Driver for SQL Server” in the list.
Important Tip
If you run into errors mentioning “ODBC” later when testing your code, it usually means ODBC wasn’t installed or configured correctly. Make sure to follow each setup step carefully, or you could end up spending hours troubleshooting something that’s easy to miss.
Jira Cloud
Your Jira instance must have the REST API enabled. This is enabled by default on Jira Cloud.
You’ll also need:
*Permission to create issues in your Jira project
*An API token (not your password)
Step 1: Create Your Jira Project or Space
This is the container where all your failed job tickets will live. Jira can be a bit confusing here since newer versions use “spaces” and “projects” almost interchangeably.
- From your Jira home screen, click the Create project button.
- Jira will ask you to choose a template. For this, a Kanban or Task tracking template is perfect.
- You’ll be asked to choose between Team-managed and Company-managed.
- Recommendation is to select Team-managed for simplicity. It lets you manage everything, including issue types, in one place.
- Give it a name like “SQL Alerts.”
- Jira will automatically suggest a “Key” (like
SJM). This key is the prefix for all issues (e.g.,SJM-1,SJM-2). - Click Create project.
Step 2: Create a Work Type (Issue Type)
Your Jira project needs a custom issue type for SQL job failures so tickets are organized and easy to filter later.
- Open your project, for example, SQL Alerts.
- In the left sidebar, click Project settings. You may need admin permissions for this.
- Select Issue types from the settings menu.
- Click Add issue type or Create issue type, depending on your Jira version.
- Give it a name, such as SQL Job Failure or SQL Alert.
- Choose the type. Select Task if you want a standard issue, or Incident if you are tracking problems.
- Add a description if you want, for example, “Created automatically when a SQL Server job fails.”
- Click Add or Create to save it.
Quick Tip
If you are using:
- Team-managed project, you can do this directly from Project settings > Issue types.
- Company-managed project, go to Jira Settings > Issues > Issue types from the main admin panel.
Step 3: Create an API token:
- Log in to Atlassian Account Settings
- Click “Create API token”
- Give it a label such as sql-automation-script
- Click “Create” and then “Copy” it.
- Save the token somewhere safe, like a vault. You will not see it again.
Install Python
You will need to install Python and take extra precautions because I found that most errors happen with Python.
Download and install on MAC
Windows Download and install:
Download Python from https://www.python.org/downloads/
During installation:
- Check “Add Python to PATH”
- Click “Install Now”
Run commands after downloading Python:
pip install pyodbc requestsVerify the installation:
python --version
pip --versionIf you get permission errors, use:
pip install --user pyodbc requestsImportant Tip
If this doesn’t work on WINDOWS, reinstall Python and make sure the PATH box is checked.
Configuring Python Script
Most errors were based on Python. So, it’s very important to install Python correctly and to install and run Python on the correct path.
Step 1: Create the Python Script
Create a new file named JobFailuresMultipleTickets.py.
Copy and paste the script from the GitHub repository.
The JobFailuresMultipleTicket script will create MULTIPLE tickets if multiple jobs fail unless the flags are set at the top.
- https://github.com/aaa-dba/JiraAutomation/blob/main/JobFailuresMultipleTickets.py
- I created debug flags at the top
- DEBUG
- True sends “this is a test” to Jira
- False will send the job failures details to Jira
- TEST_SINGLE_ISSUE
- True will only create one Jira ticket
- False will send each failure to a Jira ticket
- DEBUG
- I created debug flags at the top
Step 2: Alter Variables in Python
At the top of the script, edit the configuration variables:
# ----------------------------
# SQL Server connection setup
# ----------------------------
server = "PUT SERVER HERE"
username = "PUT USER HERE"
password = "PUT PASSWORD HERE"
# ----------------------------
# Jira API setup
# ----------------------------
jira_email = "PUT JIRA EMAIL AUTH HERE"
jira_token = "PUT JIRA API TOKEN HERE"
jira_project_key = "PUT PROJECT KEY HERE"
jira_issue_type_id = "PUT ISSUE TYPE HERE"
jira_domain = "PUT COMPANY JIRA DOMAIN HERE"Understanding the Jira Variables
Details on the variables that need to be added to the script are below. This will not work unless these are correct and permissions are set.
| Variable | Description | Notes |
| jira_email | Used for authentication with Jira | Use a service account or shared team email |
| jira_token | The API token from your Atlassian account | Store securely; don’t hard-code in production |
| jira_project_key | Short key for your project (e.g., OPS, DEV) | Found in Jira project settings or issue keys |
| jira_issue_type_id | Numeric ID for the issue type (e.g., Task) | Found in Project Settings > Issue Types |
| jira_url | REST API endpoint for your Jira site | Usually https://your-domain.atlassian.net/rest/api/3/issue |
Step 3: Run the Script
In your terminal for Windows, run:
python JobFailuresMultipleTickets.pyIn your terminal for Mac, run:
python3 JobFailuresMultipleTickets.If everything is configured correctly, you should see something like “Jira issue created successfully: OPS-123”
Check Jira and confirm that a new ticket has been created automatically.
What’s Next
Now you have the foundation in place:
- SQL Server sends data
- Python processes it
- Jira receives and tracks it
In the next phase, I’ll walk through how to schedule this script using tools like Windows Task Scheduler, cron, or Azure Automation. After that, we’ll explore how to make the SQL scripts more universal. From there, we’ll move into integrating AI to interpret and act on tickets before a human ever needs to step in.
That’s where the system starts to become intelligent. This is a pet project that will continue to grow over time. I’m currently testing other languages like Go and Next.js to see if they’re a better fit than Python. The goal is to remove the dependency on directly querying SQL Server and make the process more flexible and universal for handling other alerts and database types.
If you have other programming languages you think are better, I would love to hear about them. Mainly, I am looking for something that is not Microsoft-dependent, flexible, and easy to debug. I found that Python checks most of the boxes but had some nuances.
Leave a Reply