Using Jenkins to automate execution of SQL Server query

I’ve been playing with Jenkins to educate myself.  Finally got something that solves a problem, hopefully it can help others.  I was getting tired of manually running a query at the end of the month for one of my own reports, so I took on the project to use Jenkins to schedule the execution of a MS SQL Server query and the export of result set to .csv to drive a Tableau dashboard.

It was actually easier than I thought it would be, the only big hurdles were figuring what MS command line utilities I needed (just the two described below) and frankly, learning to install and use Jenkins was super simple.

So, heres everything material about my environment, the tools I installed and a bit about the command line args I used to run the same in Jenkins.

OS Environment –

• Windows 10

Additional tools I installed on Windows 10:

• Microsoft ODBC Driver 11 for SQL Server – Windows (download link) – Pre-reqisite for installing SQLCMD.exe
• Microsoft Command Line Utilities 11 for SQL Server (download link) – This gives you SQLCMD.exe
• Jenkins (download link) – Call SQLCMD.exe via Windows batch command in Jenkins

With MS Command line utilities installed, create a new Jenkins Project and use the following in a Jenkins ‘Execute Windows batch command’ build step.  Here is a bit about what each portion of the build step does (more info about command line args for SQLCMD.exe here):

1. Clean Up old .csv file
2. Move to the directory that has SQLCMD.exe in it
3. Log into your SQL DB
4. Run your query
5. Store the result of your query in a file delimited by commas

Here’s the same if you’d like to copy/paste for your own use:

del /F "c:\<path to your output file>\output.csv"

cd "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"

SQLCMD -S <YourSQLServerDBName> -U <YourSQLServerUsername> -P <YourSQLServerPassword> -W -Q "SELECT * from <yourSQLServerQueryHere>" > c:\delete\output.xlsx -h-1 -s,

And Jenkins makes it easy to automate and track this process as well with some really easy to use, built in reports.