SSIS Básico: Bucles ForEach para ejecutar paquetes en FileSystem

 

Es bastante común que entre los paquetes ETL que generamos para realizar una serie de procesos diseñemos un ‘Orquestador’, esto es un paquete en el que a través de tareas Ejecutar Paquete (Execute package task) establezcamos un orden de ejecución pudiendo asignar lógica variables, etc… El termino orquestador define bastante bien el propósito de este tipo de paquetes. Imaginaros un director de orquesta que da pie a cada grupo de instrumentos acompasando los ritmos, adecuando intensidad, etc… En SSIS los grupos de instrumentos serían los distintos paquetes ETL a ejecutar y el director el paquete "Orquestador”.

Es habitual como digo, pero siempre hay una primera vez para todos así que esta entrada esta dedicada a los usuarios del foro de SSIS en castellano

Que vamos a hacer?

Resolver la siguiente cuestión de Pachecora:

“Buenas tardes tengo la siguiente consulta estoy usando SQL Server 2008 R2 y lo que necesito es poder ejecutar varios package que estarán en una carpeta por medio de un Job lo que hice fue crear varios Steps para este Job que apuntan a determinado package, pero lo que necesito ahora es ejecutar todos los package dentro de una carpeta. Sera posible hacerlo?”

Antes de empezar…

Entre los distintos componentes nativos que nos ofrece Integration Services desde la versión 2005 se encuentran los bucles ForEach que son de gran utilidad para realizar iteraciones utilizando distintos enumeradores: Sistema de archivos, un dataset ADO, etc.. Para ser más claro, recorre una colección y en cada vuelta devuelve el elemento actual (algo así como un cursor en TSQL, por buscar un símil)

Escenario

Vamos a desarrollar un proyecto de SSDT2012 con plantilla de Integration Services, aunque se podría realizar con la versión BIDS 2005 y posteriores sin ningún tipo de problema.

Este proyecto consta de un solo paquete: el orquestador. Los paquetes que va a ejecutar este orquestador están desplegados en una carpeta del sistema de archivos. Realmente no nos interesa que hacen esos paquetes (en mi ejemplo están vacíos), nos vamos a ocupar de como ejecutar todos ellos a través de un punto único.

Solución

Siempre hay más de una solución a un problema, por lo que la siguiente no es sólo una de ellas.

1) Crear una variable para la ruta del paquete: Desde el proyecto de SSDT, con el paquete vacío (que he renombrado como ‘Maestro.dtsx’) que agrega la plantilla, añadimos una variable de tipo string que llamaré RutaPaquete. He añadido un valor por defecto a la variable.

El valor de esta variable será establecido en cada iteración del bucle ForEach, ahora veremos como…

2) Añadir un bucle ForEach al Control Flow, y una vez en el área de diseño editamos sus propiedades haciendo doble click. En la sección Colección debemos asegurarnos de que el enumerador seleccionado es el de archivos (Foreach File Enumerator). Cada enumerador lleva una configuración específica. En el de archivos, debemos establecer que carpeta vamos a recorrer y que tipo de archivos. La siguiente imagen muestra la configuración que he utilizado para recorrer la carpeta que contiene los archivos con extensión dtsx:

Fijaros que he seleccionado la opción “Fully qualified” como nombre de archivo. Esto me devolverá la ruta completa de cada fichero.

3) Asignar el valor a una variable. Aquí es donde entra en juego por primera vez la variable que hemos creado anteriormente (RutaPaquete). En la configuración de mapeo de variables (Variable Mappings) del bucle ForEach asignamos la variable User::RutaPaquete al resultado de cada iteración. Como este tipo de bucle sólo devuelve un resultado (el nombre del archivo) la vinculamos con el índice 0:

Y hasta aqui la configuración del bucle ForEach. Ahora vamos con la tarea Ejecutar Paquete.

4) Añadir tarea Ejecutar Paquete dentro del bucle. Hacemos doble click para editarlo y en la sección Paquete cambiamos la configuración del componente.

a) En tipo de referencia establecemos External Reference (antes de 2012 y los modelos de proyecto de despliegue de proyecto, esta parte no existía).

b) En la localización cambiamos a sistema de archivos (File System)

c) En conexión desplegamos el menú, seleccionamos nueva conexión y seleccionamos cualquier archivo. Este parámetro será sustituido por el valor de la variable, pero por ahora lo dejamos así.

Y ya estaría lista esta tarea.

Existen más opciones de configuración en esta tarea como el password de los paquetes, el mapeo de variables, si se debe ejecutar con una nueva instancia de proceso,etc.. pero para este ejemplo no necesitamos nada más. Os invito a leeros el BOL sobre esta tarea para obtener más información http://technet.microsoft.com/es-es/library/ms137609.aspx

5) Crear un expresión para la conexión. Seleccionando el Administrador de conexión de fichero que creamos en el paso 4.C y pulsando la tecla F4 accedemos al panel de propiedades. Buscamos la propiedad expresiones (Expressions) y establecemos la propiedad ConnectionString asignando la variable @[User::RutaPaquete]

Esto provocará que en cada ejecución de la tarea, la conexión tome la ruta del fichero actual dentro de la iteración del bucle.

Si ejecutamos este paquete desde el proyecto podemos comprobar que se ejecuta cada uno de los paquetes que existen en la carpeta que hemos configurado en el bucle ForEach (c:\ETL Packages). He añadido un tarea ejecutar Script con el código c# para lanzar un evento de tipo información con la finalidad de mostrar la ruta del fichero actual en cada iteración:

Ya solo quedaría configurar un Job del Agente SQL con un único paso que ejecute este paquete maestro para automatizar la ejecución de todos los contenidos en la carpeta.

Podéis descargar este paquete desde Skydrive

Espero que os haya sido de utilidad!

Saludos.