InforSupports

Historial de ejecuciones de jobs SSIS en SQL Server Agent

Written by

Francisco

in

SQL Server Agent guarda el historial completo de ejecuciones de todos sus jobs en la base de datos del sistema msdb, pero la información viene en un formato poco amigable — fechas y horas como enteros separados, duraciones codificadas como HHMMSS sin separadores, y estados representados por números. Esta consulta traduce todo eso a algo legible, enfocado específicamente en pasos de tipo SSIS.

Tablas utilizadas

TablaDescripción
msdb.dbo.sysjobsDefinición de los jobs del Agente
msdb.dbo.sysjobstepsPasos de cada job (tipo, comando, etc.)
msdb.dbo.sysjobhistoryHistorial de ejecuciones (fecha, duración, estado)

Las tres se relacionan por job_id (y step_id entre pasos e historial), ya que un job puede tener varios pasos, y cada paso acumula múltiples ejecuciones a lo largo del tiempo.

La query

USE msdb;
GO

SELECT
    j.name                                              AS JobName,
    js.step_id                                          AS StepNo,
    js.step_name                                        AS StepName,
    js.subsystem                                        AS StepType,       -- 'SSIS' para paquetes SSIS
    js.command                                          AS SSISCommand,     -- ruta del paquete o comando

    -- Fecha y hora de inicio legibles
    msdb.dbo.agent_datetime(h.run_date, h.run_time)     AS RunStart,

    -- Duración formateada HH:MM:SS
    STUFF(STUFF(
        RIGHT(REPLICATE('0', 6) + CAST(h.run_duration AS VARCHAR(6)), 6),
    3, 0, ':'), 6, 0, ':')                              AS Duration_HHMMSS,

    -- Duración en segundos (para cálculos / ORDER BY)
    (h.run_duration / 10000 * 3600)
    + ((h.run_duration / 100) % 100 * 60)
    + (h.run_duration % 100)                            AS Duration_Seconds,

    -- Fecha fin estimada
    DATEADD(
        SECOND,
        (h.run_duration / 10000 * 3600)
        + ((h.run_duration / 100) % 100 * 60)
        + (h.run_duration % 100),
        msdb.dbo.agent_datetime(h.run_date, h.run_time)
    )                                                   AS RunEnd,

    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
    END                                                 AS RunStatus,

    h.message                                           AS ResultMessage,
    j.enabled                                           AS JobEnabled

FROM msdb.dbo.sysjobs        j
JOIN msdb.dbo.sysjobsteps    js ON js.job_id = j.job_id
JOIN msdb.dbo.sysjobhistory  h  ON h.job_id  = j.job_id
                                AND h.step_id = js.step_id

WHERE js.subsystem = 'SSIS'
  -- AND j.enabled  = 1          -- descomentar: solo jobs activos
  -- AND h.run_date >= 20250101  -- descomentar: filtrar por fecha (YYYYMMDD)

ORDER BY
    j.name,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) DESC;

Cómo funciona cada parte

Fecha y hora legibles

SQL Server Agent guarda run_date y run_time como dos enteros separados (por ejemplo 20260622 y 143000). La función del sistema msdb.dbo.agent_datetime() los combina y convierte a un DATETIME normal, evitando tener que parsear los enteros a mano.

Duración: de entero codificado a HH:MM:SS

run_duration también viene como entero, pero en formato HHMMSS concatenado (por ejemplo, 13045 significa 1 hora, 30 minutos, 45 segundos). El doble STUFF() rellena con ceros a la izquierda hasta 6 dígitos y luego inserta los dos puntos en las posiciones correctas, para que se lea como una duración real.

Duración en segundos

Para poder ordenar o hacer cálculos con la duración (algo que el formato HH:MM:SS como texto no permite bien), la query descompone el mismo entero run_duration en horas, minutos y segundos por separado usando división entera y módulo, y los suma todos convertidos a segundos.

Fecha de fin estimada

Como sysjobhistory solo guarda la hora de inicio y la duración (no la hora de fin), RunEnd se calcula sumando la duración en segundos (ya calculada arriba) a la fecha de inicio con DATEADD.

Estado de la ejecución

El campo run_status es un entero sin significado evidente por sí solo. El CASE lo traduce a los cinco estados posibles que maneja SQL Server Agent: Failed, Succeeded, Retry, Cancelled e In Progress.

Filtros opcionales

La query trae dos filtros comentados, útiles según el caso:

  • AND j.enabled = 1 — descomentar para ver únicamente jobs que están activos actualmente (excluye jobs deshabilitados que quedaron en el historial).
  • AND h.run_date >= 20250101 — descomentar para acotar el historial a partir de una fecha específica, en formato YYYYMMDD. Útil en instancias con mucho tiempo de historial acumulado, donde la consulta completa puede ser lenta.

Variante: solo las ejecuciones fallidas

Para monitoreo o alertas, suele ser más útil filtrar directamente por las ejecuciones que fallaron, en vez de traer todo el historial:

-- Agregar a la cláusula WHERE de la query original:
AND h.run_status = 0  -- solo ejecuciones fallidas

Notas

  • run_duration está en formato HHMMSS como entero — requiere conversión manual, no es un tipo de dato de duración nativo.
  • agent_datetime(run_date, run_time) convierte los campos enteros a DATETIME legible.
  • Los filtros opcionales comentados permiten activar jobs y/o rango de fechas según la necesidad puntual de cada consulta.

More posts