Fala pessoal! Tudo bem com vocês? Animados para mais um post???
Hoje vamos mergulhar em um assunto muito comum de quem trabalha com integração de dados no ecossistema SQL Server: o monitoramento de execuções no SSISDB. Se você utiliza o catálogo do SQL Server Integration Services (SSIS) para armazenar os pacotes, sabe que nem sempre é fácil extrair o que realmente importa de forma rápida.
O que é o Catálogo SSISDB?
Para quem está chegando agora, o SSISDB é o banco de dados central que armazena todos os projetos, pacotes, parâmetros, ambientes e, o mais importante para este post, o histórico de execução. Quando você executa um pacote, o SQL Server registra cada passo, cada erro e cada métrica de performance dentro desse banco.
Monitorando pela Interface (SSMS)
A forma mais comum de visualizar o que aconteceu com seus pacotes é através dos relatórios nativos do SQL Server Management Studio (SSMS).
- No Object Explorer, expanda o nó Integration Services Catalogs.
- Clique com o botão direito na pasta SSISDB.
- Vá em Reports > Standard Reports > All Executions.
Embora esses relatórios sejam visualmente agradáveis, eles têm algumas limitações severas para quem precisa fazer isso com frequência:
- Analisar dezenas ou centenas de execuções: O SSMS não foi desenhado para análise em massa. Cada execução precisa ser aberta individualmente, sem visão consolidada por pacote, projeto, servidor ou período, tornando praticamente inviável identificar padrões de falha, pacotes problemáticos recorrentes ou degradação de performance ao longo do tempo.
- Lentidão: Em catálogos com milhares de execuções, os relatórios nativos fazem consultas pesadas e não indexadas na SSISDB, o que pode levar vários minutos para carregar uma simples tela, inviabilizando uso operacional em ambientes de missão crítica.
- Dificuldade de Filtro: Os filtros são extremamente limitados: não é possível filtrar por mensagem de erro, código de erro, tarefa específica, componente da pipeline, substring de mensagem, nem combinar múltiplos critérios (por exemplo: pacotes que falharam mais de X vezes no último mês).
- Troubleshooting de Erros: Para encontrar o erro real de uma execução, o usuário precisa navegar por várias camadas de telas (All Executions > Overview > Messages > Messages Internas), muitas vezes encontrando apenas mensagens genéricas antes de chegar no erro efetivo da pipeline.
- Criar relatórios: Os relatórios do SSMS não podem ser integrados ao Power BI, SSRS ou qualquer outra ferramenta de BI, impossibilitando a criação de dashboards com SLA de cargas, ranking de pacotes com mais falhas, tempo médio de execução, tendência histórica e indicadores operacionais.
- Automatizar análises: Não há como automatizar alertas, correlações ou análises avançadas (ex.: “me avise se um pacote falhar 3 vezes em 1 hora”, “identifique cargas com tempo acima do P95”, “cruze falhas com horário e servidor”), obrigando a monitoração manual.
Otimizando a Consulta com T-SQL
Para ganhar agilidade, nada supera um script bem estruturado que vai direto nas tabelas do catálogo. O script abaixo foi desenhado para trazer o histórico das últimas 100 execuções, já traduzindo os códigos de status e trazendo a mensagem de erro caso o pacote tenha falhado.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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; |
Neste script, estamos acessando as views internas do SSISDB. Note que usei um LEFT JOIN com a tabela [operation_messages] filtrando pelo message_type = 120. Isso é fundamental, pois em uma execução bem-sucedida, não teremos mensagens de erro, e não queremos que o registro suma do nosso relatório.
Se você quiser uma versão do script que retorne apenas a última linha por execução, pode utilizar esse script abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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 que usar Script em vez da Interface?
| Característica | Interface SSMS | Script T-SQL |
|---|---|---|
| Velocidade | Baixa (carregamento de interface gráfica) | Alta (execução direta no engine) |
| Customização | Limitada aos filtros fixos | Total (filtros por data, projeto, erro) |
| Automação | Impossível | Pode ser usado em Dashboards (Power BI/Grafana) |
| Histórico de Erros | Precisa navegar em sub-relatórios | Já disponível na mesma linha da execução |
Monitorar o SSISDB não é apenas sobre ver se rodou. Quando o catálogo cresce muito, as consultas de log podem começar a gerar contenção de IO e locks pesados.
Para melhorar a performance de leitura desse script em ambientes críticos:
- Índices: O SSISDB nativamente não vem com todos os índices ideais para consultas customizadas. Se você faz muito esse tipo de query, considere criar índices nas colunas de data como a start_time.
- Isolamento: Se for usar esse script em um Dashboard de monitoramento em tempo real, considere utilizar o hint WITH (NOLOCK) para evitar que a leitura dos logs bloqueie a escrita de novos logs pelos pacotes em execução.
Com esse script em mãos, você tem o poder de identificar rapidamente qual pacote falhou, por que falhou e quem foi o responsável pela execução, tudo isso sem depender da lentidão dos relatórios padrões do SSMS.
Espero que tenham gostado dessa dica, um grande abraço e até a próxima!
Portuguese 
English