SQL Server - Cómo consultar el historial de ejecución de jobs del SQL Server Integration Services Catalog (SSISDB)

¡Hola, chicos! ¿Cómo estás? ¿Emocionado por otra publicación?

Hoy vamos a profundizar en un tema muy común para quienes trabajan con integración de datos en el ecosistema de SQL Server: monitorear ejecuciones en SSISDB. Si utiliza el catálogo de SQL Server Integration Services (SSIS) para almacenar paquetes, sabrá que no siempre es fácil extraer rápidamente lo que realmente importa.

¿Qué es el catálogo SSISDB?

Para aquellos que recién llegan, SSISDB es la base de datos central que almacena todos los proyectos, paquetes, parámetros, entornos y, lo más importante para esta publicación, el historial de ejecución. Cuando ejecuta un paquete, SQL Server registra cada paso, cada error y cada métrica de rendimiento dentro de esta base de datos.

Monitoreo a través de la interfaz (SSMS)

La forma más común de ver qué sucedió con sus paquetes es a través de informes nativos. Estudio de administración de SQL Server (SSMS).

• En el Explorador de objetos, expanda el nodo Catálogos de servicios de integración.
• Haga clic derecho en la carpeta SSISDB.
• Ir a Informes > Informes estándar > Todas las ejecuciones.

Si bien estos informes son visualmente agradables, tienen algunas limitaciones graves para quienes necesitan hacer esto con frecuencia:

• Analice decenas o cientos de ejecuciones: SSMS no está diseñado para análisis de masas. Cada ejecución debe abrirse individualmente, sin una vista consolidada por paquete, proyecto, servidor o período, lo que hace prácticamente imposible identificar patrones de falla, paquetes problemáticos recurrentes o degradación del rendimiento con el tiempo.
• Lento: En catálogos con miles de ejecuciones, los informes nativos realizan consultas pesadas y no indexadas en SSISDB, que pueden tardar varios minutos en cargar una pantalla simple, lo que hace inviable el uso operativo en entornos de misión crítica.
• Dificultad del filtro: Los filtros son extremadamente limitados: no es posible filtrar por mensaje de error, código de error, tarea específica, componente de canalización, subcadena de mensaje, ni combinar múltiples criterios (por ejemplo: paquetes que fallaron más de X veces en el último mes).
• Errores de solución de problemas: Para encontrar el error real de una ejecución, el usuario necesita navegar a través de varias capas de pantallas (Todas las ejecuciones > Descripción general > Mensajes > Mensajes internos), y a menudo solo encuentra mensajes genéricos antes de llegar al error real en el proceso.
• Crear informes: Los informes SSMS no se pueden integrar con Power BI, SSRS o cualquier otra herramienta de BI, lo que imposibilita la creación de paneles con SLA de carga, ranking de paquetes con más fallas, tiempo promedio de ejecución, tendencia histórica e indicadores operativos.
• Automatizar análisis: No hay forma de automatizar alertas, correlaciones o análisis avanzados (por ejemplo: “notificarme si un paquete falla 3 veces en 1 hora”, “identificar cargas con tiempo superior a P95”, “fallas cruzadas con tiempo y servidor”), que requieren monitoreo manual.

Optimización de la consulta con T-SQL

Para ganar agilidad, nada mejor que un script bien estructurado que vaya directamente a las tablas del catálogo. El siguiente script fue diseñado para mostrar el historial de las últimas 100 ejecuciones, traducir los códigos de estado y mostrar el mensaje de error si el paquete falló.

SELECT DISTINCT TOP ( 100 )
    [A].[folder_name]                                    AS [Nm_Folder],
    [A].[project_name]                                   AS [Nm_Project],
    [A].[package_name]                                   AS [Nm_Package],
    [A].[use32bitruntime]                                AS [Fl_32Bit],
    ( CASE [A].[status]
          WHEN 1 THEN '1 - Created'
          WHEN 2 THEN '2 - Running'
          WHEN 3 THEN '3 - Canceled'
          WHEN 4 THEN '4 - Failed'
          WHEN 5 THEN '5 - Pending'
          WHEN 6 THEN '6 - Ended unexpectedly'
          WHEN 7 THEN '7 - Succeeded'
          WHEN 8 THEN '8 - Stopping'
          WHEN 9 THEN '9 - Completed'
      END
    )                                                    AS [Ds_Status],
    [A].[start_time]                                     AS [Dt_Inicio],
    [A].[end_time]                                       AS [Dt_Fim],
    DATEDIFF( SECOND, [A].[start_time], [A].[end_time] ) AS [Nr_Duracao_Segundos],
    [A].[executed_as_name]                               AS [Nm_Executor],
    [A].[caller_name]                                    AS [Nm_Chamador],
    [A].[stopped_by_name]                                AS [Nm_Parado_Por],
    [A].[server_name]                                    AS [Nm_Servidor],
    [C].[message_type]                                   AS [Nr_Tipo_Mensagem],
    [C].[message]                                        AS [Ds_Mensagem_Erro]
FROM
    [SSISDB].[internal].[execution_info]               AS [A]
    LEFT JOIN [SSISDB].[internal].[operations]         AS [B] ON [A].[execution_id] = [B].[operation_id]
    LEFT JOIN [SSISDB].[internal].[operation_messages] AS [C] ON [C].[operation_id] = [B].[operation_id]
                                                                 AND [C].[message_type] = 120 -- 120 REPRESENTA ERRO
    LEFT JOIN [SSISDB].[internal].[event_messages]     AS [D] ON [D].[operation_id] = [B].[operation_id]
                                                                 AND [D].[event_name] = 'OnError'
                                                                 AND ISNULL( [D].[subcomponent_name], '' ) <> 'SSIS.Pipeline'
-- WHERE
--     [A].[execution_id] = 11501
ORDER BY
    [A].[start_time] DESC;

En este script, accedemos a las vistas internas del SSISDB. Tenga en cuenta que utilicé un UNIRSE A LA IZQUIERDA con la mesa [mensajes_operación] filtrando a través de tipo_mensaje = 120. Esto es fundamental, porque en una ejecución exitosa no tendremos mensajes de error y no queremos que el registro desaparezca de nuestro informe.

Si desea una versión del script que solo devuelva la última línea por ejecución, puede usar este script a continuación:

SELECT TOP (100)
    [e].[folder_name]                                                AS [Nm_Folder],
    [e].[project_name]                                               AS [Nm_Project],
    [e].[package_name]                                               AS [Nm_Package],
    [e].[use32bitruntime]                                            AS [Fl_32Bit],
    CASE [e].[status]
        WHEN 1 THEN '1 - Created'
        WHEN 2 THEN '2 - Running'
        WHEN 3 THEN '3 - Canceled'
        WHEN 4 THEN '4 - Failed'
        WHEN 5 THEN '5 - Pending'
        WHEN 6 THEN '6 - Ended unexpectedly'
        WHEN 7 THEN '7 - Succeeded'
        WHEN 8 THEN '8 - Stopping'
        WHEN 9 THEN '9 - Completed'
    END                                                              AS [Ds_Status],
    [e].[start_time]                                                 AS [Dt_Inicio],
    [e].[end_time]                                                   AS [Dt_Fim],
    DATEDIFF(SECOND, [e].[start_time], COALESCE([e].[end_time], SYSDATETIME())) AS [Nr_Duracao_Segundos],
    [e].[executed_as_name]                                           AS [Nm_Executor],
    [e].[caller_name]                                                AS [Nm_Chamador],
    [e].[stopped_by_name]                                            AS [Nm_Parado_Por],
    [e].[server_name]                                                AS [Nm_Servidor],
    [m].[message_type]                                               AS [Nr_Tipo_Mensagem],
    [m].[message]                                                    AS [Ds_Mensagem_Erro],
    [m].[message_time]                                               AS [Dt_Mensagem]
FROM
    [SSISDB].[catalog].[executions] AS [e]
    OUTER APPLY
    (
        SELECT TOP (1)
            [om].[message_type],
            [om].[message],
            [om].[message_time]
        FROM
            [SSISDB].[catalog].[operation_messages] AS [om]
        WHERE
            [om].[operation_id] = [e].[execution_id]
            AND [om].[message_type] = 120
        ORDER BY
            [om].[message_time] DESC
    ) AS [m]
-- WHERE [e].[execution_id] = 11501
ORDER BY
    [e].[start_time] DESC;

¿Por qué utilizar Script en lugar de Interface?

Monitorear el SSISDB No se trata sólo de ver si funciona. Cuando el catálogo crece, las consultas de registros pueden comenzar a generar contención de E/S y bloqueos intensos.

Para mejorar el rendimiento de lectura de este script en entornos críticos:

• Índices: SSISDB de forma nativa no viene con todos los índices ideales para consultas personalizadas. Si realiza este tipo de consultas con frecuencia, considere crear índices en columnas de fecha como hora_inicio.
• Aislamiento: Si va a utilizar este script en un panel de monitoreo en tiempo real, considere usar la sugerencia CON (SIN BLOQUEO) para evitar que la lectura de registros bloquee la escritura de nuevos registros mediante la ejecución de paquetes.

Con este script en mano, tiene el poder de identificar rápidamente qué paquete falló, por qué falló y quién fue responsable de ejecutarlo, todo sin depender de la lentitud de los informes de software estándar. SSMS.

Espero que te haya gustado este tip, un fuerte abrazo y ¡hasta la próxima!