SSIS tells you whether a package succeeded. It doesn’t tell you whether the 40,000 rows it loaded are right. The fix is a check step between the load and whatever reads it, wired so a failed check stops the package.
Option 1: an Execute SQL Task
Put an Execute SQL Task after the Data Flow Task, on the same OLE DB connection, with ResultSet set to None:
SET NOCOUNT ON;
DECLARE @null_keys int, @dupe_keys int, @orphans int, @msg nvarchar(2048);
SELECT @null_keys = COUNT(*)
FROM dbo.FactSales
WHERE CustomerKey IS NULL;
SELECT @dupe_keys = COUNT(*)
FROM (
SELECT SalesOrderNumber, SalesOrderLineNumber
FROM dbo.FactSales
GROUP BY SalesOrderNumber, SalesOrderLineNumber
HAVING COUNT(*) > 1
) AS d;
SELECT @orphans = COUNT(*)
FROM dbo.FactSales AS f
WHERE f.CustomerKey IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dbo.DimCustomer AS c WHERE c.CustomerKey = f.CustomerKey);
IF @null_keys + @dupe_keys + @orphans > 0
BEGIN
SET @msg = CONCAT(N'Data quality check failed: ', @null_keys, N' null keys, ',
@dupe_keys, N' duplicate order lines, ', @orphans, N' orphaned customer keys.');
THROW 50001, @msg, 1;
END;The THROW fails the task. Connect the next task with a Success precedence constraint and a Failure constraint to a Send Mail Task or a logging step.
Two details from Microsoft’s docs. With OLE DB and ADO connection managers, the task ignores errors returned after the first result set, so this script assigns variables and returns no rows before it throws. And SET NOCOUNT ON keeps row-count messages out of the way.
This works well for five checks on two tables. It doesn’t scale to fifty tables, and it keeps no history, so you can’t see that duplicates have been creeping up all month.
Option 2: call a test tool with an Execute Process Task
The Execute Process Task runs a program and, with FailTaskIfReturnCodeIsNotSuccessValue on, fails when the exit code isn’t the SuccessValue, 0 by default. Point it at powershell.exe with a script that starts a DataOps TestGen test run through its REST API, waits, and exits 1 on failures:
param([string]$Api, [string]$SuiteId, [string]$Token)
$h = @{ Authorization = "Bearer $Token" }
$job = Invoke-RestMethod -Method Post -Uri "$Api/api/v1/test-suites/$SuiteId/test-runs" `
-Headers $h -ContentType 'application/json' -Body '{"metadata": {"system": "SSIS"}}'
$deadline = (Get-Date).AddMinutes(30)
do {
Start-Sleep -Seconds 15
$status = (Invoke-RestMethod -Uri "$Api/api/v1/jobs/$($job.id)" -Headers $h).status
} while ($status -notin @('completed', 'error', 'canceled') -and (Get-Date) -lt $deadline)
if ($status -ne 'completed') { exit 1 }
$counts = (Invoke-RestMethod -Uri "$Api/api/v1/test-runs/$($job.id)" -Headers $h).result.result_counts
if (($counts.failed + $counts.error) -gt 0) { exit 1 }
exit 0TestGen returns 202 Accepted with a job id at once, so the script polls until the job reaches completed, error, or canceled. Warnings don’t fail it on purpose. Keep the token out of the package: pass it from an SSIS sensitive parameter or read it from a vault. TestGen’s API authentication page covers tokens; on open source they come from an OAuth client.
The tests themselves aren’t hand-written. TestGen profiles the tables the package loads, 55 characteristics per column, and generates tests from 34 test types, including duplicate rows. You add Reference Match for the orphaned-key check above.
What SQL Server 2025 changes
SSIS ships with SQL Server 2025, with changes (What’s New in Integration Services 2025, checked November 3, 2026). The legacy Integration Services Service and 32-bit mode are deprecated. The Attunity CDC components, the Microsoft Connector for Oracle, and the Hadoop tasks are gone. Neither check pattern above depends on them.
Seeing the package and the tests together
DataOps Observability has a documented SSIS agent, and TestGen exports results to it, so the package run and the tests on the table it loaded sit on one timeline. The Data Factory answer shows the same pattern for ADF.
Open-source TestGen is free for one user, one connection, and one project. Start at the SQL Server install page.