En versiones anteriores de SSIS, 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.
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 SSIS 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 ahora sobre los dashboards de ejecución y rendimiento que nos ofrece el servidor de Integration Services en SQL Server 2012, más adelante 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.

nivelesdebloggingPS

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):

catalog-propertiesPS

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 añadir el parámetro, 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)

dashboard-ssis-3PS

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

dashboard-divisiones-ssis-3PS-1024x571

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):

resumen-de-ejecuciones-de-un-paquete-ssis-3-1024x539

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

view-messages-ssis-3-1024x533

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 (antes conocido como BIDS) cuando activamos el seguimiento de todos los eventos que tiene un paquete SSIS.
Esto significa muchísimos mensajes, que sólo deberíamos analizar en modo “Verbose” 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.
Si vamos al informe de rendimiento (View Performance):
informe-de-rendimiento-ssis-3-1024x535
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:
data-flow-component-information-ssis-3-1024x329
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 de informe del evento PipelineComponentTime que podemos analizar desde SQL Server Data Tools para nuestros paquetes SSIS.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):
all-validations-ssis-3-1024x317
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:
all-executions-ssis-3-1024x359
También todas las operaciones ejecutadas sobre el servidor, incluyendo ejecuciones, despliegues y validaciones de paquetes (All Operations)
all-operations-ssis-3-1024x498
É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 pero sin el filtro de las últimas 24 horas.

Administrando SSIS mediante el API T-SQL

Como hemos ido viendo a lo largo del artículo, en SQL Server 2012 se introduce la nueva arquitectura para Integration Services, con una base de datos dedicada y un nuevo modelo de despliegue basado en esta arquitectura. Dentro de esta base de datos dedicada se encuentra el catálogo. El catálogo es un esquema dentro del cual están contenidas vistas y procedimientos almacenados que nos harán la vida mucho más fácil a la hora de gestionar nuestros paquetes SSIS, la estructura de nuestro servidor y mucho más.
Vamos a ir desglosando los diferentes apartados del catálogo, como se puede interactuar con él mediante código T-SQL y como esto puede modificar nuestras maneras de trabajar con SQL Server Integration Services, simplificándolas y haciéndonos más efectivos.

Explorando el catálogo de SSIS

El catálogo de Integration Services 2012 tiene multitud de vistas y procedimientos almacenados que podemos utilizar, como por ejemplo:
Gestión de proyectos:
– catalog.deploy_project
– catalog.delete_project
– catalog.restore_project
Ejecución de paquetes
– catalog.create_execution– catalog.start_execution
Parámetros
– catalog.set_object_parameter_value– catalog.set_execution_parameter_value


Se acabaron las cajas negras, llegan los data tap

A partir de SQL Server 2012 podemos incluir, incluso en tiempo de ejecución, los llamados data tap a una ejecución de un paquete de Integration Services.
Se trata de un punto del flujo de datos donde se coloca una “fuga” de los mismos y se vuelca a un fichero externo. De esta manera, podemos analizar qué datos fluían por esa rama en ese momento dado.
Para utilizar un data tap deberemos ser miembros del rol sysadmin en la instancia de SQL Server sobre la que ejecutamos el script T-SQL y permisos de ssis_admin sobre la base de datos de SSIS. Además, deberemos tener permisos MODIFY sobre la instancia de ejecución.
Los estableceremos desde T-SQL mediante el procedimiento almacenado:
exec catalog.add_data_tap @execution_id, 'task_package_path',
   'dataflow_path_id_string', 'data_filename', 'max_rows', 'data_tap_id'
OUTPUT

Siendo los parámetros:
– @execution_id: Es una variable donde almacenamos el identificador único asociado a esa ejecución concreta del paquete SSIS en nuestra instancia SQL Server 2012.
– task_package_path: Es la ruta del DataFlow donde queremos ubicar el DataTap. Por ejemplo: ‘PackageData Flow Task’
– data_flow_path_id_string: Es la sección concreta del DataFlow de donde queremos extraer el flujo de datos. Podemos encontrar esta ruta en la propiedad IdentificationString del path sobre el que queremos establecer el DataTap. Por ejemplo: ‘Paths[Lookup.Lookup Match Output]’
lookup-match-output-ssis-3
– data_filename: Es la ruta y el nombre del fichero donde queremos almacenar los datos que extraemos del DataFlow. Por defecto, todos los ficheros de DataTap se generan en <drive>:carpeta_de_instalacion_SQL_serverMicrosoft SQL Server110DTSDataDumps. Por ejemplo:  ‘DataTapDemo.csv’
– max_rows: Límite de filas que almacenará el fichero. Se recomienda establecer una cantidad moderada para evitar la generación de ficheros excesivamente grandes.
– data_tap_id: Parámetro de salida que devuelve el ID asignado al DataTap que se acaba de crear. Es útil para hacer referencia al data tap con su ID concreto en un punto posterior en el script.

Aplicaciones prácticas

Vamos a ver un ejemplo completo para iniciar la ejecución de un paquete SSIS mediante el API T-SQL habiendo añadido un data tap. Vamos a utilizar conceptos a los que hemos hecho referencia en los capítulos anteriores de esta serie, como por ejemplo los entornos en el servidor SSIS o los niveles de logging para controlar el nivel de detalle de la información que produce el paquete SSIS al ser ejecutado.

 use SSISDB
go
/*EJEMPLO DE EJECUCIÓN DE UN PAQUETE DESPLEGADO DESDE TSQL*/
–BD utilizada: SSISDB
 /* Comprobamos el id de la referencia de entorno para el entorno que queramos (en caso de utilizar un entorno). Lo podremos utilizar para asignar el parámetro @reference_id al crear la ejecución del paquete SSIS */
select * from catalog.environment_references 
   --Generamos el identificador de ejecucion
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'nombre_paquete.dtsx',
 @execution_id=@execution_id OUTPUT, @folder_name=N'nombre_carpeta', @project_name=N'nombre_proyecto',
 @use32bitruntime=False, @reference_id=15 /*utilizamos el id=15 como ejemplo, dependerá del entorno que queramos utilizar*/ 
--Comprobamos el id de ejecucion
select @execution_id as id_ejecucion  --Logging level:
--0 -> none
--1 -> basic
--2 -> performance
--3 -> verbose  --Establecemos el nivel de logging a "verbose" para capturar toda la información posible
DECLARE @var0 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50,
 @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0  --Volcado en caso de error = 0
DECLARE @var1 bit = 0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50,
 @parameter_name=N'DUMP_ON_ERROR', @parameter_value=@var1  /*Añadimos un data tap obteniendo 2000 filas como máximo.
Todos los data taps se vuelcan por defecto en "<drive>:carpeta_donde_este_instalado_SQLServerMicrosoft SQL Server110DTSDataDumps" */
exec catalog.add_data_tap   @execution_id, 'PackageData Flow Task',
 'Paths[Lookup.Lookup Match Output]', 'DataTapDemo.csv', '2000'  --Iniciamos ejecución
EXEC [SSISDB].[catalog].[start_execution] @execution_id

Analizando el comportamiento del servidor

Además de gestionar la estructura del servidor, sus carpetas y proyectos y la ejecución de paquetes SSIS con todas sus variantes (data taps, parámetros, entornos, logging, etcétera), también podemos analizar que ha sucedido en el servidor mediante las vistas del catálogo de SSIS.
Estaremos consumiendo la misma información de la que se nutren los dashboards que veíamos en el apartado anterior pero podremos personalizar la manera en la que lo hacemos, ya que podremos construir nuestras consultas en T-SQL.
Al igual que los procedimientos almacenados, tenemos vistas para cada apartado de nuestro servidor. Tenemos entre otras:
– catalog.executions
– catalog.environments
– catalog.projects– catalog.packages
A partir de ellas podemos consultar temas de estructura del servidor o, por ejemplo, de rendimiento:
/*ANALISIS GLOBAL DE LAS FASES DE EJECUCIÓN DE UN PAQUETE SSIS (identificado por @execution_id)*/
SELECT task_name, subcomponent_name, execution_path, phase, start_time, end_time
FROM catalog.execution_component_phases
WHERE execution_id = @execution_id
ORDER BY task_name, subcomponent_name, execution_path

/*AGREGADO Y CALCULO POR FECHAS DE INICIO Y FIN*/
SELECT task_name, subcomponent_name, execution_path, phase,
SUM(DATEDIFF(ms,start_time,end_time)) as active_time_ms,
DATEDIFF(ms,min(start_time), max(end_time)) as total_time_ms
FROM catalog.execution_component_phases
WHERE execution_id = @execution_id
GROUP BY task_name, subcomponent_name, execution_path, phase
ORDER BY task_name, subcomponent_name, execution_path, phase
Fundamentalmente estamos utilizando la información que generan los paquetes SSIS en sus ejecuciones (cuyo detalle, recordemos, se define por su logging level). En estas consultas estamos condensando la información que también podíamos ver cuando seleccionábamos ver todos los mensajes de una ejecución concreta en nuestros dashboards del servidor SSIS. Como ya comentábamos cuando hablábamos de dashboards, estamos analizando los datos que podíamos ver en versiones anteriores de SSIS mediante el evento PipelineComponentTime en el registro de eventos de Integration Services.

Usando las vistas del catálogo podremos nutrir cualquier aplicación que esté en una capa superior con resultados de consultas T-SQL y analizar nuestros datos de la manera que queramos ya sea mediante listados, reportes gráficos o incluso construyendo un sistema analítico. Cada uno es libre de elaborar sus propias locuras a partir del punto de partida 
Además de los dashboards que veíamos anteriormente, como ejemplo de sistema construido como una capa por encima del catálogo tenemos el proyecto de SQL Server Reporting Services de Jamie Thompson en CodePlex (http://ssisreportingpack.codeplex.com/) el cual está disponible para descargar gratuitamente.

Conclusiones

SQL Server Integration Services 2012 nos trae gran cantidad de novedades de gran utilidad, bien sea para mejorar la velocidad de nuestros desarrollos o para añadir funcionalidad y valor a los mismos.
Todos estos cambios se enmarcan dentro de la perspectiva que Microsoft ha dado a sus herramientas de Inteligencia de Negocio, acercándolas a usuarios con un perfil menos técnico para dar lugar a curvas de aprendizaje más suaves y cortas. Así, entramos desde todos los puntos de la plataforma en el concepto de self-service BI, aunque en el caso de Integration Services 2012 este acercamiento no es tan significativo como en las capas analítica o de reportes.
Junto con el resto de novedades en la plataforma de Inteligencia de Negocios de SQL Server así como con las de motor relacional nos ayudarán en gran medida a generar soluciones más robustas y que resuelvan mejor y más rápido las necesidades de nuestros clientes o usuarios.

Referencias

Guías de conversión y despliegue de paquetes en SSIS 2012:
http://msdn.microsoft.com/en-us/library/hh213290(v=sql.110).aspx
http://msdn.microsoft.com/en-us/library/hh231102(v=sql.110).aspx
Consejos para una migración exitosa a SSIS 2012
http://technet.microsoft.com/en-us/library/hh667275.aspx
El listado completo y referencia técnica sobre los procedimientos almacenados y las vistas disponibles para la gestión del servidor SSIS mediante T-SQL se puede encontrar en http://msdn.microsoft.com/en-us/library/ff878003.aspx
Co-Autor de este post: Víctor M. García Sánchez.
Pau Sempere
Últimas entradas de Pau Sempere (ver todo)