SQL Server Agent

How to use CAT CLI in SQL Server Agent?

We tend to show results of CAT automated data tests in various CI/CD pipelines, or results of a manual run. Recently we got a request to run CAT in the context of SQL server agent.

Does it make sense? Sure! Whenever you need to check whatever with your data (such as between steps of a SQL Server agent jobs), just run CAT.

Install CAT

OK, the agent will run CAT. That means, CAT must be on the machine. You have two options:

  • install CAT on the machine prior the SQL server agent runs it

  • let SQL Server agent install it.

When choosing first option, you need to consider a few things:

  • will the agent “see” CAT? Install it as PS Module with -Scope AllUsers.

  • think about upgrades, align the process of updating CAT with your overall strategy for SW upgrades.

The option that let’s the agent install CAT for himself is a bit easier.

PowerShell module

If you have access to the Internet on the server, just create a step that runs:

Install-Module CAT -AcceptLicense -Force -AllowClobber -Scope CurrentUser

It will install the latest version of CAT PowerShell module. If you require specific version (reocommended), add -RequiredVersion 0.0.0 (with specific version).

That’s it! Just run Invoke-CatProject in the next step or in a separate SQL Server agent job.

CAT CLI

Download CAT CLI from the Internet:

$version = "0.22.0.12"
$url = "https://docs.justcat.it/releases/cat-cli-$version-setup.exe"
Invoke-WebRequest -Uri $url -OutFile "$HOME/Downloads/cat-cli-$version-setup.exe"

To install CAT for the SQL Server agent user:

$version = "0.22.0.12"
$logPath = Join-Path $pwd "cat-cli-setup.log"
& "$Home\Downloads\cat-cli-$version-setup.exe" /VERYSILENT /LOG="$logPath"
Write-Output "Installed CAT CLI, log file is here: $logPath"

Run Automated Tests

Then then next step is to run the tests. It makes sense to separate jobs for installing/upgradig CAT and for actually running your tests. The code for running the tests looks like this for PowerShell:

Import-Module CAT
Invoke-CatProject "$Home/Projects/DWH/DwhTests.cat.yaml"

or for CAT CLI:

catcli run -p "$Home/Projects/DWH/DwhTest.cat.yaml"

Considerations

SQL Server agent is clearly different environment than CI/CD pipelines. It has no GUI for displaying the results of the tests. Use CAT’s output options to store the results of the tests in MS Excel, MS SQL server, ORACLE, … See Outputs.

← Azure DevOps Classic Releases
CAT on Linux →