SSIS – Ejemplo de Package con origen de datos Excel dinámicos

Estimados amigos lectores, en éste, mi primer post sobre SSIS, quiero presentarles un ejemplo que seguramente les será muy útil si están dando sus primeros pasos con esta herramienta de Microsoft. Intentaré explicar con un ejemplo sencillo como crear un Package de SSIS (SQL Server Integration Services) para importar los datos de todos los documentos XLS (MS Excel) que se encuentran en un directorio particular, procesarlos y luego enviar los datos obtenidos y procesados a una tabla de una Base de datos de SQL Server. En post posteriores iré ampliando este Package, agregando más transformaciones para complejizarlo y ajustarlo más a la realidad de la vida cotidiana, pero para empezar, creo que este ejemplo estará más que bien. Bueno, arranquemos…
El ejemplo constará de 3 partes con la finalidad de simplificar su comprensión. Estas son:

1. Crear Orígenes y destino de Datos
2. Creación del Package sin iteración
3. Creación de iteración para el Package

1. Crear Orígenes y destino de Datos
Lo primero que debemos hacer es contar con 3 archivos XLS (Excel) en una carpeta de nuestra PC. Les sugiero que utilicen los mismos nombres y path que yo para evitar problemas. Si miran la imagen de abajo, verán el path y los nombres de archivos. Por ejemplo:
C:\SSIS\Alumnos\Alumnos ddmmaaaa.xls
Respetar el formato del nombre será importante para futuros post de ampliación del ejemplo. Fíjense que lo único que difiere en ese nombre es el año.


La estructura (columnas) del los 3 archivos debe ser igual, no así los datos de los alumnos, los que sólo deben ser diferentes los valores de la columna Curso en cada uno de los archivos. Por ejemplo, el Alumno Baresi Mateo en el archivo Alumnos 28022006.xls tendrá en la Columna “Curso” el valor 1, en el archivo Alumnos 28022007.xls el valor 2, y en el archivo Alumnos 28022008.xls el valor 3. La estructura del XLS debe ser la siguiente:


La tabla donde se almacenarán los datos obtenidos y transformados para este ejemplo se encuentra en la base de datos Examples y se llama TablaAlumnos (Tener presente que tanto la BD como la tabla han sido creadas para este ejemplo). La estructura de la tabla sería la siguiente:


Donde RowID será la clave principal y autoincremetal.

2. Creación del Package sin iteración
Una vez que ya contamos con los archivos de “origen” de datos y la tabla de “destino” creada procedemos a abrir el SQL Server Business Intelligence Development Studio para comenzar a crear el proceso. Creamos un nuevo Proyecto de Integration Services, le ponemos de nombre IMPORT_XLS y lo guardamos dentro de la carpeta SSIS que creamos con anterioridad.


Luego de creado el proyecto, se nos abre en el IDE del VS el paquete por defecto, cuyo nombre es Package.dtsx. Allí es donde debemos apelar a nuestra creatividad y comenzar a “dibujar” nuestro proceso!!!
Lo primero que vamos a hacer es crear las conexiones de origen y destino para los datos. Para crear el origen de datos de archivos Excel nos situamos en el Administrador de Conexiones, allí hacemos lo siguiente:
- click derecho en la superficie, seleccionamos la opción New Connection…
- Seleccionamos la opción EXCEL, y hacemos clic en Add….
- Luego hacemos click en el botón Browse y seleccionamos el archivo deseado, en nuestro caso seleccionaremos el archivo Alumnos 28022006.xls ubicado en C:\SSIS\Alumnos
- Dejamos chequeada la casilla First row has column name, ya que nuestras columnas tienen encabezado.
- Hacemos click en el botón OK y ya habremos creado el “Origen” de datos. Se llamará Excel connection Manager.


Para crear el “Destino” de datos haremos lo siguiente:
- click derecho en la superficie, seleccionamos la opción New OLE DB Connection…
- en la ventana de configuración hacemos click en la opción New… para crear una nueva conexión a la BD.
- En la nueva ventana de Connection Manager debemos especificar los datos de nuestra conexión. Para nuestro ejemplo, los datos pueden observarse en la figura siguiente.

- Damos Ok en las dos ventanas y ya tendremos creada también la conexión de destino hacia la base de datos llamada localhost.Examples.

Ahora procederemos a la creación del Package propiamente. Lo primero que haremos es insertar en el Control Flow (Flujo de Control) un nuevo Data Flow task (Tarea de Flujo de Datos), arrastrando dicho objeto hacia la superficie que aún se encuentra vacía.
Posteriormente, hacemos doble click sobre el Data Flow task para poder agregarle la funcionalidad adecuada. Una vez dentro del Data Flow, agregamos una Excel Source, ubicada en la sección Data Flow Sources de la Toolbox arrastrándola a la superficie. Hacemos click derecho sobre el elemento agregado, elegimos la opción Edit… Allí nos aseguramos que la conexión seleccionada sea la que creamos con anterioridad y luego sólo seleccionamos la hoja del fichero XLS que contiene la información (en nuestro caso seleccionamos Hoja1$)


Lo siguiente que haremos es agregar el destino de datos, para ello arrastraremos el componente OLE DB Destination desde la Sección Data Flow destination. Una vez insertado en el Package, nos situamos sobre el Excel Source y arrastramos la flecha verde hasta el OLE DB Destination que acabamos de insertar.



Lo siguiente que debemos hacer es Editar el OLE DB Destination y donde debemos en la opción Connection Manager nos aseguramos que la conexión sea la que creamos con anterioridad (localhost.Examples) y seleccionamos la TablaAlumnos. Luego debemos ir a la opción Mappings para relacionar cada una de las columnas de Excel con sus respectivos campos en TablaAlumnos.



Es muy posible que les surjan errores de compilación debido a que no se pueden mapear campos con diferentes tipos de datos. En nuestro caso, los errores surgen en algunos campos. Lo que debemos hacer es fijarnos en el OLEDB Destination los tipos de datos que corresponden a cada campo (posicionando el cursor unos segundos encima del mismo) y luego agregar un nuevo componente para realizar el cambio en el tipo de datos.

El componente a agregar es Derived Column, con el cual podremos crear columnas derivadas con el nuevo tipo de dato. El Data Flow debe quedar como se muestra en la figura siguiente.



Luego debemos Editar el componente Derived Column insertado, agregando columnas derivadas para cada uno de los campos que requieren transformación, para ello vea la siguiente figura y observe que la propiedad Length de de la columna derivada “DerivedDivision” es 1. Eso es porque puede ir solo un carácter en este caso.



Una vez ingresadas estas las columnas, damos click en OK para confirmar estos cambios. Lo que resta en esta primera parte es volver a mapear los campos del destino con problemas de tipo de datos con las nuevas columnas derivadas que acabamos de crear. Para ello debemos editar el componente OLEDB Destination, ir hasta la opción Mappings y allí vincular las nuevas columnas con sus respectivos campos en el destino.


Si damos OK en la ventana, ya habremos concluido esta parte del ejercicio y podremos llevar a cabo la primera prueba. Presionamos F5 para que ejecutar el Package y una vez concluida la ejecución, si nos dirigimos a la tabla TablaAlumnos de la Base de Datos podremos ver los datos importados. Deberíamos tener los siguientes datos:




3. Creación de iteración para el Package

Vamos por buen camino, ya falta menos!!! Hasta ahora el proceso funciona correctamente pero con sólo un fichero XLS. Lo que haremos ahora es agregar las iteraciones para que recorra todo el directorio C:\SSIS\Alumnos, cargando de esa forma los datos de todos los ficheros en la tabla TablaAlumnos.

Debemos situarnos en el Control Flow donde agregaremos un Foreach Loop Container sobre la superficie donde ya se encuentra el Data Flow Task. Luego arrastramos éste último dentro del componente Foreach Loop Container.



Ahora debemos configurar el Foreach, para ello hacemos click derecho sobre el mismo, y elegimos Edit. En la ventana del editor nos situamos en la opción collections en donde debemos poner el path y el tipo de archivo que vamos a recorrer. Donde debemos poner “C:\SSIS\Alumnos” y “*.XLS” respectivamente.



En la opción Variable Mappings debemos agregar una nueva variable que se llamará varFileName, será de tipo String y su valor será el primero de los ficheros XLS que tenemos en el directorio “c:\SSIS\Alumnos\Alumnos 28022006.xls”


Lo último que resta es configurar la conexión para el origen de datos Excel Connection Manager que creamos con anterioridad. Para ello hacemos click derecho sobre él, Propiedades, y en Expressions asignamos la variable varFileName a las propiedades ExcelFilePath y ServerName.


Damos OK y listo! El proceso ya está concluido.

Si ejecutamos nuevamente el proceso (es conveniente eliminar el contenido de la tabla TablaAlumnos antes de cada importación para evitar duplicación de datos) veremos cómo han sido cargados los datos de los 3 ficheros XLS de la carpeta C:\SSIS\Alumnos.

Espero les haya sido útil este ejemplo y en sucesivos posts iré ampliando este ejemplo con más funcionalidades. Saludos a todos los lectores!