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. Podéis encontrar información acerca de estos cambios en los artículos anteriores de esta serie.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.
A lo largo de este artículo iremos 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.
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.
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 taps
A partir de SQL Server 2012 podemos incluir, incluso en tiempo de ejecución, los llamados data taps 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 vuelcan 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]’
– 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.
Ejecutando paquetes desde T-SQL
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. 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.
Básicamente estaremos consumiendo la misma información de la que se nutren los dashboards que veíamos en el capítulo 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, disponemos de vistas para cada apartado de nuestro servidor. Como ejemplo tenemos:
- catalog.executions
- catalog.environments
- catalog.projects
- catalog.packages
A partir de ellas podemos consultar la estructura del servidor o, por ejemplo, tener un informe 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 nivel de detalle, recordemos, viene dado por su logging level). En estas consultas estamos condensando la información que también podíamos ver pormenorizada cuando seleccionábamos ver todos los mensajes de una ejecución concreta en nuestros dashboards del servidor SSIS. Como ya comentábamos en el capítulo sobre dashboards SSIS de esta misma serie, estamos analizando los datos que podemos 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 J
Además de los dashboards que veíamos en el capítulo anterior, 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/) del cual ya está disponible para descargar gratuitamente la versión 0.3.
Referencias
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
Resumen del API T-SQL por parte del grupo de producto de SSIS: http://blogs.msdn.com/b/mattm/archive/2011/08/12/ssis-t-sql-api-overview.aspx
