En versiones anteriores de SQL Server Integration Services, si queríamos tener un control de las ejecuciones de nuestros paquetes SSIS teníamos que construir algún tipo de framework alrededor de los mismos para registrar información como el tiempo de ejecución, los fallos ocurridos, las conexiones que utilizaba el paquete que falló, etc.

[box type=”info”] Este artículo pertenece a la serie “Novedades de Integration Services en SQL 2012”. Puedes encontrar el índice de artículos al pie de este.[/box]

En SQL Server 2012 tenemos una base de datos dedicada a SSIS y, en ella, el catálogo de SSIS, un esquema que contiene tablas y procedimientos almacenados con los que interactuar de una manera más completa y consistente que en anteriores versiones. Esta nueva arquitectura permite un mejor análisis de lo que está ocurriendo en las ejecuciones de los paquetes en nuestro servidor.

Tenemos varias vías para éste análisis:

• Consultando directamente las vistas y procedimientos presentes de manera nativa en el catálogo.
• Utilizar el catálogo como fuente para nuestros reportes personalizados.
• Utilizar los reportes presentes en el dashboard que SSIS nos ofrece a través de la nueva BD.

Hablaremos en este artículo sobre los dashboards de ejecución y performance que nos ofrece el servidor de Integration Services en SQL Server 2012, y en el siguiente capítulo de la serie veremos cómo explotar la información del catálogo directamente mediante consultas TSQL.

Niveles de logging

Integration Services nutre los informes presentes en el dashboard con la información que hay en el catálogo. Ésta información cambiará en parte su nivel de detalle dependiendo del nivel de logging que especifiquemos en la ejecución de nuestros paquetes. Tenemos 4 niveles:

• None: No guardamos información acerca de la ejecución del paquete
• Basic: Se guarda información básica (tiempos totales, informes de fallo, etc.)
• Performance: Se guarda la información básica y además, información relacionada con el rendimiento del paquete (tiempos parciales de ejecución por componente, etc.)
• Verbose: Se guarda absolutamente toda la información que un paquete produce (incluyendo todos los eventos: validación, ejecución, progreso, post-procesamiento, etc.)

 

Elegiremos el nivel de logging en la pestaña Advanced de la ventana de ejecución de un paquete SSIS ubicado en el servidor.

 

A partir de la versión RC0 (Release Candidate 0) de SQL Server 2012 podemos elegir también el nivel de logging por defecto a nivel de servidor en las propiedades del catálogo (botón derecho sobre el catálogo –> Properties):

En el caso de la ejecución directa desde el catálogo (botón derecho sobre el paquete –> Execute) el nivel de logging efectivo será aquel que especifiquemos en la pestaña Advanced (Figura 1) de la interfaz de ejecución del paquete. El nivel de logging por defecto a nivel de servidor entra en acción cuando no especificamos nivel de logging a nivel de paquete (al invocar una ejecución del paquete desde el procedimiento almacenado correspondiente en el catálogo de SSIS sin concretar el nivel de logging mediante parámetros, por ejemplo)

Además, hay información que siempre se registrará independientemente del nivel de logging establecido a nivel de servidor o paquete. Por ejemplo, aquellas validaciones de paquetes que ejecutemos sobre los paquetes almacenados en el servidor haciendo clic con el botón derecho sobre el paquete –> Validate.

El dashboard de SSIS

Ahora que ya sabemos cómo modificar la información que vamos a registrar en las ejecuciones de nuestros paquetes, vamos a ver cómo podemos examinarla utilizando el dashboard de SSIS.

Haciendo clic sobre la BD dedicada de SSIS podremos elegir la opción Reports y, desde ahí, ir a la página principal del dashboard (Integration Services Dashboard) o a algún informe concreto (Standard Reports)

 

Seleccionamos Integration Services Dashboard para ver el dashboard principal e iremos navegando por los distintos informes desde él.

Podemos dividir el dashboard en:

A) Resumen de las ejecuciones de paquetes en las últimas 24 horas (fallidos, en ejecución, con éxito, otros)

B) Enlaces al resto de los informes.

C) Información sobre las conexiones existentes en los paquetes que fallaron en las últimas 24 horas.

D) Listado detallado de los paquetes ejecutados.

Podemos así identificar de un vistazo que ha ido ocurriendo en nuestro sistema en las últimas 24 horas.

Dentro del listado detallado de ejecuciones en las últimas 24 horas podríamos ir al resumen de las ejecuciones de ese paquete (Overview):

 

Dentro de Overview, podríamos analizar todos y cada uno de los mensajes que ha producido el paquete para esa ejecución (View Messages):

 

En la ejecución de nuestro paquete hemos especificado un nivel de logging “Verbose”, por tanto en el registro de mensajes tendremos absolutamente todos los mensajes que un paquete SSIS es capaz de producir durante su ejecución. Es decir, el equivalente a analizar el log de eventos que podemos ver en SQL Server Data Tools cuando activamos el seguimiento de todos los eventos que tiene un paquete SSIS.

Esto significa muchísimos mensajes, que sólo deberíamos analizar si queremos desentrañar qué está pasando dentro de nuestro paquete SSIS con gran detalle. En el ejemplo que aparece en este artículo el informe tiene 14 páginas de mensajes, y es un paquete sencillo, con apenas 6 componentes. Es recomendable ajustar el nivel de logging a nuestras necesidades de análisis. Si no elegimos el nivel adecuado podemos encontrarnos sin información que examinar o con un exceso de la misma que complique sobremanera la detección de las situaciones o problemas que estamos buscando.

Si vamos al informe de rendimiento (View Performance):

Tendremos una gráfica que nos muestra la evolución de los tiempos de ejecución de las últimas ejecuciones de ese paquete en concreto, cuándo se ejecutó, la media de tiempo de ejecución para todas las ejecuciones registradas con su desviación típica, e incluso si miramos un poco más abajo en el informe:

 

Veremos un desglose de tiempos por componente para poder analizar en detalle el rendimiento de cada uno de ellos para la ejecución seleccionada, cuál nos está provocando un cuello de botella si es que lo hay, etc. Visto de otra manera, es un resumen con un formato amigable de la información que obtendríamos del evento PipelineComponentTime, el cual podemos analizar también desde SQL Server Data Tools para nuestros paquetes SSIS mediante el log de eventos.

Volviendo a los reportes a nivel de servidor, tenemos también todas las validaciones de paquetes que se efectúen de forma explícita (botón derecho sobre el paquete en el servidor –> Validate):

 

Todas las ejecuciones (no sólo de las últimas 24 horas) de paquetes en el servidor SSIS, con los enlaces de Overview, Messages y Performance disponibles y un resumen de su ejecución y su duración:

 

También todas las operaciones ejecutadas sobre el servidor, incluyendo ejecuciones, despliegues y validaciones de paquetes (All Operations):

 

Éste último reporte es especialmente útil desde el punto de vista de auditoría para comprobar quién y en qué momento realizó una acción concreta. Nos puede ayudar de manera significativa a controlar qué hay en nuestro servidor y quién es el responsable de los cambios, algo que en versiones anteriores era difícil de controlar cuando había más de un usuario autorizado.

Finalmente, tenemos el reporte de las conexiones utilizadas en los paquetes fallidos, para identificar rápidamente los orígenes de datos que pueden estar causándonos problemas. Es un reporte idéntico al resumen que teníamos en el apartado (D) del dashboard general (Figura 4) pero sin el filtro de las últimas 24 horas.

Conclusiones

En SQL Server 2012 tenemos la posibilidad de acceder de manera rápida y sencilla a una serie de completos reportes para tener una visión general de que está sucediendo en nuestro servidor SSIS. Además, tenemos la opción de profundizar en las ejecuciones individuales de los paquetes SSIS para analizar la razón por la que han fallado, por qué se están ejecutando más lentos de lo normal, quién ha subido la última versión al servidor, etc.

Todo esto nos ahorra una gran cantidad de trabajo, las necesidades básicas de logging y reporte de ejecuciones están cubiertas bajo el dashboard de Integration Services y los informes que podemos encontrar en él.

Aun así, al disponer de las vistas y los procedimientos almacenados del catálogo de SSIS podemos construir nuestros propios reportes o analizar mediante código TSQL los diferentes aspectos de nuestro servidor SSIS. En próximo capítulo de esta serie veremos cómo podemos consumir el catálogo para extraer la información que deseemos de manera personalizada, acorde con nuestras necesidades.