Running tSQLt Tests in a Jenkins Pipeline

Running tSQLt tests manually in SSMS is fine for development, but the real value comes from automating them in a CI/CD pipeline. This guide walks through integrating tSQLt test execution into a Jenkins declarative pipeline with proper failure detection and artifact output.

What You Will Need

• Jenkins server with the SQL Server JDBC driver or SQLCMD available
• A SQL Server test database with tSQLt installed
• Jenkins credentials configured for database access
• The JUnit plugin for Jenkins (to parse test result XML)

The Core Approach

tSQLt can output results as XML using tSQLt.RunWithXmlResults. The output follows a JUnit-compatible schema, which Jenkins can parse and display natively. The pipeline runs SQLCMD to execute the tests, captures the XML output, saves it as a file, then uses Jenkins' junit step to process it.

Sample Jenkinsfile

pipeline {
  agent any
  environment {
    DB_SERVER   = 'your-sql-server'
    DB_NAME     = 'YourTestDatabase'
    DB_CREDS    = credentials('sql-server-creds')
  }
  stages {
    stage('Deploy Schema') {
      steps {
        sh '''
          sqlcmd -S $DB_SERVER -d $DB_NAME \
                 -U $DB_CREDS_USR -P $DB_CREDS_PSW \
                 -i deploy/schema.sql
        '''
      }
    }
    stage('Run tSQLt Tests') {
      steps {
        sh '''
          sqlcmd -S $DB_SERVER -d $DB_NAME \
                 -U $DB_CREDS_USR -P $DB_CREDS_PSW \
                 -Q "EXEC tSQLt.RunWithXmlResults; \
                      SELECT TestResult FROM #TestResults" \
                 -o test-results.xml
        '''
      }
      post {
        always {
          junit 'test-results.xml'
        }
      }
    }
  }
  post {
    failure {
      echo 'tSQLt tests failed. Check the test results tab.'
    }
  }
}

Handling Failures Correctly

By default, tSQLt does not throw an exception when tests fail — it just records the failure. This means your pipeline stage will exit with code 0 even if tests failed. You need to explicitly check the results table and fail the build if any tests failed.

-- Add this after RunWithXmlResults to fail the build on test failure
IF EXISTS (
  SELECT 1 FROM tSQLt.TestResult WHERE Result = 'Failure'
)
  RAISERROR('One or more tSQLt tests failed.', 16, 1);

Azure DevOps and GitHub Actions

The same approach applies to Azure DevOps pipelines and GitHub Actions. Azure DevOps has a native PublishTestResults task that accepts JUnit XML. GitHub Actions can use the dorny/test-reporter action to display results. The SQL execution step is the same — only the pipeline syntax changes.