Jaa

Como utilizar Integration Services como fuente de datos de Reporting Services

  • Artikkeli

Reporting Services nos permite crear, de una manera rápida y sencilla, informes a partir de una consulta a una fuente de datos. Es decir, podemos crear un informe que nos muestre una consulta en particular (una query T-SQL o un Stored Procedure) de una base de datos corriendo en SQL Server 2005.

Una de las características de Reporting Services es la capacidad de soportar diferentes tipos de fuentes de datos. Como ya viene siendo habitual, podemos extraer información de fuentes de datos "clásicas", como son OLE DB, ODBC, SQL Server u Oracle. Además de estas fuentes de datos relacionales, también podemos extraer los datos de fuentes de datos jerárquicas, ficheros XML, y fuentes de datos dimensionales, Analysis Services. Como añadido a todas estas fuentes de datos, Reporting Services (SSRS) puede conectarse a Integration Services (SSIS) y utilizarlo como fuente de datos. Cada vez que generemos de nuevo un report que utilize SSIS como fuente de datos, SSRS lanzará una petición a SSIS provocando que éste ejecute el paquete (unidad de ejecución de SSIS) de nuevo.

Sin más, vamos a pasar a explicar los pasos que tenemos que dar para habilitar esta funcionalidad y luego explicaremos como montar paso a paso un proyecto ejemplo que ilustre esta funcionalidad. Al final explicaremos un poco más los beneficios y posibles usos de esta solución.

Activación de la fuente de datos SSIS

Una vez tenemos instalados tanto SSRS como SSIS en la misma máquina, tendremos que "destapar los ojos" a SSRS para que pueda utilizar a SSIS como fuente de datos. Para ello seguiremos los siguientes pasos:

  1. Nos dirigiremos al directorio <drive>:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies y abriremos el fichero RSReportDesigner.config con un editor de texto.
  2. Habilitaremos el diseñador de reports para utilizar SSIS como fuente de datos. Para ello en la sección <Data> descomentaremos la siguiente línea:
    <!-- <Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/> -->
  3. Repetiremos la descomentación en la sección <Designer> para la línea: <!-- <Extension Name="SSIS" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> -->
  4. Guardamos el fichero y salimos. Acordaros de guardaros una copia de seguridad antes de modificar cualquier fichero .config. ¡Más vale prevenir que curar!
  5. El siguiente paso será activar la extensión SSIS de SSRS que permitirá la interacción entre ambos. Para ello nos dirigiremos a la carpeta <drive>:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer y abriremos el fichero RSReportServer.config con el bloc de notas
  6. Localizaremos la siguiente entrada y la descomentaremos:
    <!-- <Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/> -->

Solución de ejemplo

Llegados a este punto ya tenemos tanto al diseñador de reports como a la extensión de SSRS capacitadas para lidiar con SSIS. Vamos a crear un pequeño ejemplo para ver la funcionalidad.

El primer paso será crear un proyecto SSIS (o SSRS) y añadirle a la solución creada un nuevo proyecto SSRS (o SSIS). Aunque no es necesario tener estos dos proyectos en la misma solución, nos ayudará a entender mejor el ejemplo.

Configuración del paquete SSIS

Seguiremos los siguientes pasos en el proyecto SSIS:

  1. Crearemos un nuevo 'Connection Manager' haciendo click con el botón derecho en la zona inferior en la pestaña 'Control Flow' del paquete 'Package', que se creó automáticamente para nosotros
  2. Seleccionamos 'New OLE DB Connection...'
  3. Configuraremos la conexión para que se conecte a la base de datos Adventure Works de nuestra instancia de SQL Server
  4. En la pestaña 'Control Flow' añadiremos un elemento de tipo 'Data Flow' (lo podemos encontrar en la barra de herramientas 'Toolbox' a la izquierda de la pantalla)
  5. En la pestaña 'Data Flow' añadiremos un data source de tipo OLE DB arrastrándolo desde la Toolbox
  6. También añadiremos un 'Data Flow Destination' del tipo 'DataReader Destination' desde la misma Toolbox (los 'Data Flow Destinations' se encuentran abajo del todo de la Toolbox)
  7. Para darle un poco de juego, añadiremos una tarea de transformación del tipo 'Derived Column'

Configuramos la fuente de datos 'OLE DB Source'. Hacemos doble click sobre ella y seguimos los siguientes pasos:

  1. Nos aseguramos que la está seleccionada la conexión que hemos definido antes y seleccionamos la tabla [Person].[Contact]
  2. En el panel izquierdo seleccionamos 'Columns'. En esta ventana seleccionamos que columnas del origen queremos importar. Para el ejemplo únicamente dejaremos seleccionadas las columnas Title, FirstName, MiddleName, and LastName.
  3. Click Ok

Configuramos la transformación 'Derived Column'. Esta transformación nos permite actualizar valores de la fuente de datos mediante funciones. Estas "actualizaciones" pueden sobreescribir el valor original o redirigirse a una columna nueva. Lo que nosotros vamos a hacer es juntar las columnas FirstName y LastName en una nueva columna llamada FullName. Para ello seguiremos los siguientes pasos:

  1. Seleccionamos 'OLE DB Source' con un simple click. Arrastramos su flecha verde hasta la caja de la transformación 'Derived Column'.
  2. Doble click sobre 'Derived Column'. En el panel inferior, en la columna 'Derived Column Name' escribimos FullName
  3. Dejamos el valor <add as a new column> en la columna 'Derived Column'.
  4. En la columna 'Expression' escribimos la siguiente expresión: [FirstName] + " " + [LastName] (los nombres de las columnas pueden ser arrastrados y soltados desde el panel superior)
  5. Click Ok

Como último paso configuraremos el destino 'DataReaderDest'. Lo primero que haremos, con el fin de mejorara la claridad de lectura es modificar su nombre y le pasaremos a llamar simplemente DataReader. La configuración seguirá los siguientes pasos:

  1. Enlazamos la transformación 'Derived Column' con el destino 'DataReader' mediante la unión por la flecha verde saliente de la transformación. Esto enviará los datos resultantes de la transformación a su destino.
  2. Doble click sobre 'DataReader'. En la pestaña 'Input Columns', seleccionamos todas las columnas que nos aparecen.
  3. Click Ok

El paquete SSIS ya está listo para ser utilizado desde SSRS. Para verificar que todo está correcto, ejecutaremos el paquete y tenemos que obtener 19,972 filas.

Creación del report que utiliza SSIS como fuente de datos

Todos los reports tienen como base una consulta a una fuente de datos. En el caso que utilizemos SSIS la fuente de datos será el paquete que hemos creado, el fichero .dtsx físicamente, y la consulta será el nombre que le hayamos dado al destino de datos, en este caso 'DataReader'. Vamos a ver como configuramos todo esto.

En el proyecto SSRS añadiremos un report sin la intervención del wizard. Para ello haremos click con el botón derecho sobre la carpeta 'Reports' y seleccionaremos la opción Add > New Item. En la ventana 'Add New Item' seleccionaremos el objeto 'Report' (para la esta demostración el nombre Report1.rdl nos servirá).

La configuración de la fuente de datos seguirá los siguientes pasos:

  1. En la pestaña 'Data', seleccionaremos el valor '<New Dataset...>' del desplegable 'Dataset:'
  2. Cambiaremos el tipo (Type) al valor SSIS
  3. En la caja de texto 'Connection string:' introduciremos el path completo de nuestro paquete .dtsx todo entrecomillas dobles y precedido por el modificador -f y un espacio. Por ejemplo: -f "c:\Demos\SSRS\SSIS\Integration Services Project1\Package.dtsx"
  4. Click Ok
  5. En el área principal, donde de normal se escribiría la consulta T-SQL, teclearemos el nombre del destino de datos del paquetes .dtsx. En este caso: 'DataReader'
  6. Para comprobar el correcto funcionamiento, simplemente haremos click sobre el símbolo de admiración.

Como podéis comprobar SSIS nos han devuelto todas las columnas originales además de la nueva columna definida, FullName. Para finalmente tener un informe con estos datos únicamente nos queda crear una tabla en la pestaña 'Layout' que muestre los vuestros datos y publicar el informe. Estos últimos pasos no los explicaremos en este post al no ser su objetivo

Funcionalidad y usos de la interconexión entre SSRS y SSIS

Una de las principales ventajas de SSIS es su ejecución pipeline en memoria. Esto quiere decir que los datos van pasando a través de las diferentes transformaciones a medida que se van leyendo. Así para obtener el primer valor en la salida del paquete no se debe esperar a que el último valor haya sido procesado (existen excepciones que ya comentaremos en un post futuro cuando hablemos de SSIS). Una manera análoga con el mundo real de comprender este funcionamiento es pensar en la naturaleza o física de los líquidos. Si dejamos caer un flujo de agua escaleras abajo veremos como el agua va precipitándose escalón tras escalón sin esperar al resto del agua que hay por detrás. El líquido fluye a través de los escalones de la misma manera que los datos lo hacen a través de las transformaciones de SSIS.

La ventajas de este modo de funcionamiento son principalmente dos. En primer lugar este funcionamiento tiene un rendimiento muy bueno, a aprovechar perfectamente las capacidades de procesamiento multihilo de los procesadores y sistemas actuales. En segundo lugar, todo el procesamiento se hace en memoria y no necesitamos almacenes físicos de datos intermedios; agilizando la ejecución y abaratano costes.

Cuando SSRS está interconectado con SSIS lo que ocurre es que SSRS hace una llamada a SSIS cada vez que ha de generar el informe (una vez generado el informe puede ser configurado para permanecer en memoria caché). SSIS al recibir la petición por parte de SSRS ejecuta el paquete especificado pasando los datos a SSRS.

Un escenario típico de uso de este tipo de soluciones es cuando se quiere obtener informes de fuentes de datos que requieren de un tratamiento previo, pero que no se desea guardar una copia de los datos transformados. Estos datos pueden provenir de fuentes de datos OLE DB, ODBC, Oracle, etc. Eliminando la necesidad de una copia intermedia de los datos transformados ahorraremos en costes, complejidad, mantenimiento y desincronización.

Las capacidades de transformación de datos y ejecución pipeline de SSIS junto a la facilidad y flexibilidad en la generación de informes de SSRS forman una buena pareja de baile para afrontar una gran variedad de escenarios de reporting diferentes.

Espero que os haya servido de ayuda y buen ejemplo,

JB

Comments