Your pipeline lives in msdb, not in git

Where the pipeline actually lives

Why connecting a repository doesn't help

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. 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.

   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;

2. 2

   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.

   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;

3. 3

   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.

   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;

4. 4

   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.

   -- 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. 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.

   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

What Decim does with all this

Common questions