Solutions · SQL Server & SSIS

Your pipeline lives in msdb, not in git

SSIS keeps parameters in SSISDB. SQL Agent keeps job steps in msdb. Transformation logic sits in stored procedures with no commit history. A repository shows part of the pipeline; the rest has to be read from the server.

Request a demo See how it works

Fig. 1 — Committed definition vs. deployed definition

The two graphs describe the same pipeline. Only one of them runs.

Where the pipeline actually lives

The reflex when investigating an ETL failure is to open the repository. On a SQL Server estate that reflex is usually wrong, because the repository holds a minority of the things that determine what the pipeline does.

A typical nightly load is a SQL Agent job with eight steps. Step one runs a stored procedure. Step four executes an SSIS package with parameters supplied by an environment reference. Step six calls a command-line utility somebody wrote in 2018. The schedule lives in msdb, the parameters live in SSISDB, the procedure body lives in sys.sql_modules, and only the utility is in git.

This is not unusual or a sign of a badly run team. It is what SQL Server ETL looks like almost everywhere, and it is precisely why the modern data-observability tools cannot help — they assume a warehouse, a transformation framework, and a repository that describes both.

Where each part of a SQL Server pipeline is defined, and whether source control can see it

What	Where it lives	In git?
Job schedule and steps	`msdb.dbo.sysjobs`, `sysjobsteps`	No
Execution history	`msdb.dbo.sysjobhistory`	No
SSIS package logic	`.dtsx` in a repo, deployed `.ispac` in SSISDB	Partly
SSIS parameters & environments	`SSISDB.catalog`	No
Transformation logic	`sys.sql_modules` (stored procedures)	Sometimes
Table & column schema	`sys.tables`, `sys.columns`	Only if migrations exist
Linked server definitions	`sys.servers`	No
Deadlock evidence	`system_health` extended events	No
Application ETL code	Your repository	Yes

Why connecting a repository doesn't help

Even when a repository exists and is well maintained, three gaps remain, and each one has caused incidents that took days to explain.

The deployed thing is not the committed thing

An .ispac deployed to SSISDB in March does not update because someone merged a change in June. The catalogue tells you what is actually deployed; the repository tells you what somebody intended.

Parameters are not code

The same package, executed with a different connection manager or batch size, is effectively a different pipeline. Environment references and parameter values are configuration held in SSISDB, and they are frequently changed without any corresponding commit.

Procedures often have no repository at all

A stored procedure altered directly in production leaves a modify_date and nothing else. There is no diff, no author, no pull request, and no way to correlate the change with an incident — unless you read the definition and compare it against what you expected.

How to find what actually ran

Five queries that answer most SQL Server ETL questions. Everything here is read-only and safe to run on a production instance during an incident.

1
List the jobs and their steps

Start in msdb. A SQL Agent job is an ordered list of steps, each with a subsystem and a command. The command column is the actual thing that executes — a stored procedure call, a package path, a command line. This is the closest thing to a pipeline definition that exists on most SQL Server estates.
msdb — jobs and their steps
```
SELECT  j.name           AS job_name,
        j.enabled,
        s.step_id,
        s.step_name,
        s.subsystem,          -- TSQL, SSIS, CmdExec, PowerShell...
        s.command
FROM    msdb.dbo.sysjobs      AS j
JOIN    msdb.dbo.sysjobsteps  AS s ON s.job_id = j.job_id
WHERE   j.name LIKE '%ETL%'
ORDER BY j.name, s.step_id;
```

Pull the execution history

sysjobhistory holds one row per step per execution, plus a summary row where step_id = 0. Two gotchas: run_date and run_time are integers, so use msdb.dbo.agent_datetime() to combine them; and run_duration is HHMMSS packed into an integer, not a count of seconds — 130 means one minute thirty, not two minutes ten.

msdb — execution history for one job

SELECT  j.name AS job_name,
        h.step_id,
        h.step_name,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) AS started_at,
        h.run_duration,        -- HHMMSS as an integer, not seconds
        CASE h.run_status
             WHEN 0 THEN 'Failed'
             WHEN 1 THEN 'Succeeded'
             WHEN 2 THEN 'Retry'
             WHEN 3 THEN 'Canceled'
             WHEN 4 THEN 'In progress'
        END AS status,
        h.message
FROM    msdb.dbo.sysjobhistory AS h
JOIN    msdb.dbo.sysjobs       AS j ON j.job_id = h.job_id
WHERE   j.name = 'ETL - Transaction Import'
ORDER BY h.instance_id DESC;

Get the SSIS parameters that were actually used

If a step's subsystem is SSIS, the package logic may be in source control but the values it ran with are not. SSISDB.catalog.execution_parameter_values records what each execution was actually given. When the same package behaves differently in two environments, this table usually holds the answer.

SSISDB — executions and their parameter values

SELECT  e.execution_id,
        e.folder_name,
        e.project_name,
        e.package_name,
        e.start_time,
        e.end_time,
        e.status              -- 4 = failed, 7 = succeeded
FROM    SSISDB.catalog.executions AS e
WHERE   e.package_name = 'LoadTransactions.dtsx'
ORDER BY e.start_time DESC;

-- The parameters that run actually used, which the .dtsx does not record
SELECT  parameter_name,
        parameter_value,
        value_set
FROM    SSISDB.catalog.execution_parameter_values
WHERE   execution_id = 41821;

Read the stored procedure

On a large proportion of estates the real transformation is T-SQL inside a procedure that was never committed anywhere. sys.sql_modules holds the text. Searching its definition for a table name is the fastest way to answer “what writes to this?” — a question no repository can answer if the procedure isn't in one.

sys.sql_modules — find and read procedure source

-- Which procedure writes to this table?
SELECT  o.name          AS procedure_name,
        o.modify_date,
        LEN(m.definition) AS definition_length
FROM    sys.sql_modules AS m
JOIN    sys.objects     AS o ON o.object_id = m.object_id
WHERE   o.type = 'P'
  AND   m.definition LIKE '%dbo.SalesDaily%'
ORDER BY o.modify_date DESC;

-- Then read it. This is frequently the only copy that exists.
SELECT  m.definition
FROM    sys.sql_modules AS m
JOIN    sys.objects     AS o ON o.object_id = m.object_id
WHERE   o.name = 'usp_LoadSalesDaily';

5
Reconcile the counts

Finally, compare what arrived with what landed, and look at the distribution of whatever was rejected rather than the total. Random corruption spreads across values; a mapping gap concentrates on one. When a single discriminator accounts for most of the rejections, you are looking at a value the system has never seen before rather than a data quality problem.
Rejected rows, grouped by discriminator
```
SELECT  PaymentType,
        COUNT(*) AS Rejected
FROM    dbo.RejectedTransactions
WHERE   RunId = 18291
GROUP BY PaymentType
ORDER BY Rejected DESC;
```

Failure modes specific to SQL Server ETL

Across a lot of incidents the same half-dozen shapes recur. What makes them tractable is that each has a distinctive tell — a signature in the data that separates it from the others long before you understand the cause.

Failure mode	The tell	How to confirm it
Silent row rejection	Run reports success; target row count is short	Group the reject table by its discriminator column — concentration on one value means a mapping gap, not corruption
Retry duplicates	Duplicate key count exactly equals one batch size	Check `sysjobhistory` for the same `step_id` appearing twice within minutes
Deadlock victim	Msg 1205, job fails mid-load	Read the deadlock graph from `system_health` and identify the winning session
Empty input, clean exit	Run finishes in seconds, loads nothing, reports success	Compare `run_duration` against the trailing median for that step
Parameter drift	Same package, different behaviour per environment	Diff `execution_parameter_values` between the good run and the bad one
Schema change upstream	Parse or conversion errors on every row	Compare `sys.columns` against the last known-good run's landing table

The one that costs the most is the first. A run that reports success and diverts rows to a table nobody monitors will not alert, will not fail a data quality test — because the invalid rows never reached the table being tested — and will typically be discovered at month end by an accountant rather than an engineer.

We wrote about that pattern in detail in a separate article on successful runs that lose rows, including the query that identifies it.

Decim SQL Server today

What Decim does on SQL Server today

Decim does not connect to your instance. It reads the code, the logs and the ticket, and writes the database queries for your team to run.

Everything above is work you can do by hand, and experienced engineers do it by hand during incidents, repeatedly. Decim follows the same order of operations: find the step, read its logs against healthy runs, weigh the explanations, then settle them with evidence. It keeps a record of each move, so the conclusion arrives with everything that supports it.

What it can read on a SQL Server estate today is narrower than the full list of places a pipeline lives, and it is worth being exact about the difference. The table below is the current boundary.

SQL Server evidence, and how Decim reaches it today

Evidence	How Decim reaches it	Status
Stored procedures, job scripts and migrations committed to git	Read from the GitHub repositories you select. Each procedure that moves data becomes a step in the discovered pipeline, cited by file and line range	Today
Commits and pull requests	Read from GitHub and used to connect a change to the first bad run	Today
ETL and application logs	Google Cloud Logging sources you define as filters, or log files on a Windows host read through the optional agent	Today
Windows Application event log	Read through the optional agent. SQL Server writes its errors there, and SQL Agent writes job outcomes there when a job is set to	Today
The incident ticket and attached files	Jira Cloud issue imported as a snapshot; exports, scripted procedures and screenshots attached to the incident	Today
Row counts, reject tables, `sysjobhistory`, SSISDB parameter values	A read-only query Decim writes and your team runs. Decim validates it and never executes it	Via a check
Procedures that exist only in `sys.sql_modules`	Not seen by discovery. Script the procedure out and attach it, or answer a check that asks for it	Via a check
Reading `msdb`, `SSISDB` and `sys.sql_modules` directly	Native SQL Server, SQL Agent and SSIS connectors	Planned

Discovery: the pipeline as the repository describes it

Connect the GitHub App and choose the repositories that hold your ETL. Decim reads them and finds the pipelines in each one, each defined by its trigger: a schedule or cron expression, a Hangfire job, a queue consumer, a file arrival, a batch command. For each pipeline it builds a step graph of sources, transformations and destinations. A stored procedure committed as a .sql file is a transformation like any other, and every step carries the file and line range it was found in.

Each connection between steps is marked direct, when the code shows it outright, or inferred, when Decim worked it out from naming or shared tables. Steps where pipelines tend to break, such as a filter on a parameter or a reject branch, are flagged as points of interest. A job that is defined only in msdb, with no script in the repository, is not discovered yet.

Logs: what the run said about itself

If your ETL writes to Google Cloud Logging, you define named log sources as filters, for example “Ledger load jobs”, using a read-only logging.read grant. If the logs are files on a Windows server, the optional Windows agent lists the directory and reads the files in byte ranges, and it can read the Windows Event Log, where SQL Server records its errors. The agent polls outbound for tasks and runs no SQL.

Checks: evidence that lives in the database

Row counts, reject tables, job history and SSIS parameter values live in the database, which Decim does not read. When one of them would settle a question, the investigation writes a check: a read-only query, validated against a read-only allowlist, that your team runs with its own access. The result goes back into the incident and starts a new revision of the hypotheses. A check can also be a file to fetch or a short set of guided steps.

Example Sample data

A worked example: the missing EUR rows

Sample data from the demo workspace, not a customer case. It is a SQL Server pipeline whose transformation logic is stored procedures kept in git.

The report reads: “Transaction volume dropped by 38% in the nightly ledger load. Finance says the EUR totals have been missing since Tuesday.” It links Jira ticket FIN-2291, “EUR totals missing from the daily finance dashboard since Tuesday”, whose description and comments are imported as evidence.

The pipeline

Discovered from github.com/acme/ledger-etl, Nightly ledger load runs at 02:00 UTC. It extracts the core banking ledger through a REST export, loads it into stg_transactions with the stored procedure stg.usp_load_transactions, converts currencies in a second procedure that joins the ECB daily FX rates feed, writes fact_transactions in the warehouse, and feeds the finance daily report. A normal night carries about 111,000 entries: roughly 41,000 EUR, 58,000 USD and 12,000 GBP. Losing every EUR row is a drop of 37 to 38 per cent, which matches the report.

Locate

Decim matches the report to Nightly ledger load at 91%, noting that it is the only pipeline writing fact_transactions, and starts at stg_transactions, citing the procedure lines that made the match.

Read the logs, step by step

The staging step ends as found something: EUR rows stop arriving in staging on Tuesday. The failing run logs a line for USD and a line for GBP, then reports success with zero errors. There is no EUR line. The previous night, which serves as the baseline, has one.

Cited log lines, stg_transactions log

# failing run, Tuesday: log source ledger-nightly, step 3
2026-10-13 02:14:09 usp_load_transactions Loaded 57911 rows for USD
2026-10-13 02:14:09 usp_load_transactions Loaded 12206 rows for GBP
2026-10-13 02:14:09 usp_load_transactions Step completed successfully (0 errors)

# baseline, the previous night
2026-10-12 02:13:52 usp_load_transactions Loaded 40903 rows for EUR

Each cited line is stored with its byte offsets, the excerpt and a hash, so anyone reviewing the finding can open the source and check it. The other two steps end as looked fine: the extract still carries 40,887 EUR rows out of 111,004, and currency conversion processed 70,117 rows with none unmatched.

Weigh explanations

Revision 1 holds three hypotheses. Each lists the evidence for and against it and the test that would settle it, and an independent critique pass challenges all three.

Hypotheses for the missing EUR rows, across two revisions

Hypothesis	Revision 1	Revision 2	What settled it
A. A change on Tuesday excluded EUR in staging	61%	Confirmed, 92%	Commit `9f3c2e1`, the cited log lines, and the row counts from the check
B. The FX rates file arrived late and conversion dropped EUR	48%	Rejected, 18%	The FX file landed before conversion started; every currency pair had a rate
C. The core banking export stopped delivering EUR	35%	Rejected, 9%	The export still carries about 41,000 EUR rows a night

Settle it

The deciding fact is how many rows per currency reached staging each night, and that is in the database. Decim writes the check; the team runs it and returns the result.

The check: staging row counts by currency sql

-- Check: rows loaded into staging per currency, per day, either side of Tuesday.
-- Read-only. Validated against a read-only allowlist; Decim does not run it.
SELECT  CAST(loaded_at AS date) AS load_date,
        currency,
        COUNT(*)                AS rows_loaded
FROM    dbo.stg_transactions
WHERE   loaded_at >= DATEADD(day, -7, CAST(SYSUTCDATETIME() AS date))
GROUP BY CAST(loaded_at AS date), currency
ORDER BY load_date, currency;

The result shows EUR dropping to zero on Tuesday while USD and GBP hold steady. EUR does not appear as a zero row: a GROUP BY over rows that never arrived returns nothing for them, which is why an absence has to be read against the night before.

Conclude

Revision 2 confirms hypothesis A at 92%. Commit 9f3c2e1, Tuesday's release, replaced the hard-coded list ('USD','GBP','EUR') with a parameter @Currencies defaulting to 'USD,GBP'. The scheduled job calls the procedure without the parameter, so every EUR row is filtered out, and the step still reports success with zero errors. The two rejected hypotheses stay on the record with the evidence that ruled them out.

Fix

The remediation has two parts. A repository patch restores EUR in the default and passes the list explicitly from the job step. An SQL repair gives the team a diagnostic SELECT of EUR rows per day since the release, one bounded reload of the affected nights, and a regression SELECT comparing EUR counts with the export. Decim runs none of it.

Draft pull request #482: Restore EUR in the ledger currency filter diff

--- a/sql/stg/usp_load_transactions.sql
+++ b/sql/stg/usp_load_transactions.sql
 CREATE OR ALTER PROCEDURE stg.usp_load_transactions
-    @Currencies NVARCHAR(200) = 'USD,GBP'
+    @Currencies NVARCHAR(200) = 'USD,GBP,EUR'
 AS

One click opens the patch as draft pull request #482 on GitHub. The patch is checked to apply cleanly but never built or run, and nothing is merged automatically. The incident exports as a PDF report with the timeline, root cause, evidence and verification.

What Decim does not do yet

Decim does not connect to SQL Server, run SQL, or read msdb, SSISDB or sys.sql_modules. Pipeline discovery sees the repository, so a procedure altered only in production, or a job whose steps exist only in SQL Agent, is outside it until you attach the definition or answer a check. The agent does not redact, and evidence is processed by a third-party language-model provider.

Native connectors for SQL Server, SQL Agent and SSIS are planned, along with redaction. Until then, the five queries earlier on this page are the kind of thing the checks ask for, and they are worth having ready.

Common questions

Does Decim need access to my SQL Server databases?

Not today. Decim does not connect to databases. When the evidence that decides an investigation is inside one, it writes a read-only query, validated against a read-only allowlist, and your team runs it with its own credentials and returns the result to the incident. Decim never executes the query. A native SQL Server connector is planned.

Do I have to put my stored procedures in source control first?

For pipeline discovery, yes. Decim discovers pipelines from code in GitHub, so a procedure kept in the repository becomes a step with its file and line range, and one that exists only in sys.sql_modules is not seen. You can still script such a procedure out and attach it to an incident, and the investigation can ask for it through a check. Reading sys.sql_modules directly is part of the planned connector.

How does Decim handle SSIS packages?

Package files kept in the GitHub repository are part of the code Decim reads; there is no SSIS-specific parser. The deployed catalog and its execution parameter values are not read today. When those values decide the question, the check Decim writes can be a query against SSISDB.catalog.execution_parameter_values for your team to run. A native SSIS connector is planned.

Do the queries on this page work on SQL Server 2012 or 2014?

Yes. sysjobs, sysjobsteps, sysjobhistory, sys.sql_modules and the SSISDB.catalog views have been stable since 2012. Because Decim does not connect to the instance, the SQL Server version does not constrain it; the checks it writes are plain read-only T-SQL that your team runs.

Does the Windows agent need to run on the database server?

No. The agent is optional and does not touch the database at all. It does three things: list a directory, read a file in byte ranges, and read the Windows Event Log. Put it on whichever Windows x64 host holds the ETL logs you want read. It polls outbound for tasks and accepts no inbound connections.

What leaves my network?

Code from the repositories you selected, log entries from the Cloud Logging sources you defined, the files and event-log entries the agent was asked to read, the Jira snapshot and anything you attach. Query results reach Decim only when your team returns them. Evidence is processed by a third-party language-model provider and is not redacted today. Raw agent payloads are purged three days after an incident closes and attachments after seven; cited excerpts stay with the record. See security.

Get started

Point it at a SQL Server job that failed last week

Bring an incident you already know the answer to. If Decim reaches the wrong conclusion, that tells you more about whether to trust it than any demo we could script.

Request a demo Read the docs