Monitorar o tempo de execução de projetos do SSIS é uma tarefa essencial para garantir que os dados estão sendo carregados corretamente e no tempo previsto, principalmente quando uma aplicação está aguardando o retorno destas execuções.

O SSIS oferece alguns reports padrões que mostram o tempo utilizado nas últimas execuções, porém estes reports não são nem um pouco úteis quando precisamos monitorar processos que rodam dezenas de vezes a cada hora. Na figura abaixo, podemos observar um dos reports padrões que o SSIS oferece. Ele mostra o tempo decorrido nas últimas dez execuções que finalizaram com sucesso.

Esse report pode ser utilizado para uma simples conferência, mas precisamos automatizar o monitoramento, e para resolver esse problema vamos utilizar o banco SSISDB. O mesmo é criado quando configuramos o integration services na instância e é responsável por armazenar todas as informações relacionadas aos projetos do SSIS, como, por exemplo, versões, propriedades, execuções, dados de conexão, etc.

Vamos utilizar a tabela catalog.executions. Ela armazena todas as instâncias dos packages que foram criadas e informações da execução, como, por exemplo, o status da execução, data de criação, a versão do package, etc.

Para calcular qual é o tempo de execução, vamos consultar a coluna start_time, que armazena a data e hora em que a instância do packge foi executada, e utilizando a função datediff vamos obter a diferença em segundos do início da execução com a data atual, conforme:

declare @foldername as varchar(50) = ''
declare @packagename as varchar(50) = ''
declare @executiontime int = 0 
  
select @executiontime = datediff(s, cast(start_time as datetime), getdate()) 
from ssisdb.catalog.executions 
where status = 2  
and folder_name = @foldername 
and package_name = @packagename 

Nos filtros, utilizamos o nome da pasta, a qual o projeto foi salvo, o nome do package e o status. A coluna status grava o estado atual de cada execução e pode apresentar diversos valores. Utilizamos o valor 2 que representa running, ou seja, vai retornar somente o tempo de execução para o package que ainda está executando. Ressaltando que esses filtros podem ser alterados conforme as peculiaridades de cada projeto.

Você deve estar se perguntando, como podemos notificar ao operador que o ETL está executando a mais tempo que o planejado? Simples, podemos utilizar a stored procedure sp_send_dbmail passando como parâmetro um profile do Database Mail, os destinatários, assunto e o corpo do e-mail contendo todas as informações necessárias.

if @executiontime > 180
begin
 
exec msdb.dbo.sp_send_dbmail
    @profile_name = 'OperadorTeste', 
    @recipients = '[email protected]', 
    @body = 'O package pckTeste está executando a mais de 3 minutos. (Tempo médio de execução 10 segundos).', 
    @subject = 'Tempo de execução pckTeste'
 
end

Por fim, adicionamos esses scripts em um job do SQL Agent e configuramos uma schedule. Assim toda vez que o job executar e o tempo de execução do package for maior do que o planejado, o operador será notificado.

Gostou do artigo técnico? Ficou com dúvida? 

 

Por Paula Cerutti Ferreira e Luciano Gambato