CAT in SQL Server Agent
A SQL Server Agent job can run CAT like any other program: check the data between two steps of a load, or run the whole test project on the Agent's schedule. Two things to get right — the step type, and the account the key and the environment variables belong to.
Before you start
SQL Server Agent is a scheduler, and CAT recognizes it as one: an unattended run needs the Enterprise plan and a license key on the machine — from CAT 3.0 a job step without the key stops before any test runs — see Integrations. The key, like the environment variables the project file reads, is per user: set it under the account the job step runs as (the SQL Server Agent service account, or the proxy account of the step), not under your own.
Install CAT on the server
Install once, as a normal server component, and upgrade it the way you upgrade anything else there — pinned, not “latest”:
No PowerShell, no runtime to manage — the natural choice for a job step:
winget install DataTools.CATCLI --version 3.0.0 # the version you tested with - pin it
or the signed installer from the Releases page, run silently: cat-cli-3.0.0-setup.exe /VERYSILENT. Then, as the account the job runs under: catcli instance --setLicenseKey "<key>".
PowerShell 7 must be on the server (the Agent’s own PowerShell step type runs Windows PowerShell, which CAT does not support — see the step type below). Install for every user, so the Agent’s account sees it:
Install-Module CAT -RequiredVersion 3.0.0 -AcceptLicense -Force -Scope AllUsers
Then, as the account the job runs under: Set-CatInstance -LicenseKey '<key>'.
The job step
Use the Operating system (CmdExec) step type, not the PowerShell type — the Agent’s PowerShell step runs Windows PowerShell (the SQLPS host), and the CAT module needs PowerShell 7. CmdExec runs whatever you would type at a prompt:
catcli run -p "D:\CAT\DWH\DwhTests.cat.yaml"
pwsh -NoProfile -Command "Import-Module CAT; Invoke-CatProject -Path 'D:\CAT\DWH\DwhTests.cat.yaml'"
The step’s Run as account (a proxy with the Operating system (CmdExec) subsystem, or the service account) is the one that needs: the license key set, the environment variables the project file references, access to the data sources, and write access to where the outputs go.
Results and the job’s verdict
The Agent has no test report; ask the project for outputs — a database output is the natural one here, one row per test into a table on the same server, written as the tests finish. To make the job step fail on failed tests, make the step read the result: CAT itself exits 0 when the run completed (see Results and exit codes), so either a following T-SQL step queries the results table and RAISERRORs, or the PowerShell step ends with if ((Get-CatTestResultSummary).FailedCount -gt 0) { exit 1 }.
Related
- Integrations — plan, key, tool, secrets, results.
- Use environment variables — machine-level variables are what a service account sees.
- CAT in Windows Task Scheduler — the same job without a SQL Server.