This post has already been read 236 times!
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
- Microsoft Command Line Utilities 11 for SQL Server (download link) – This gives you
- Jenkins (download link) – Call 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 here):
- Clean Up old .csv file
- Move to the directory that has in it
- Log into your SQL DB
- Run your query
- 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>\" 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\ -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.