SSIS- Package con origen de datos Excel dinámicos


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!



REFERENCIAS :

http://mciacci.blogspot.pe/2008/03/ssis-ejemplo-de-package-con-origen-de.html
http://bifase.blogspot.pe/2011_01_01_archive.html
http://es.slideshare.net/nesegohv/datawarehouse1
http://blogs.solidq.com/es/business-analytics/curso-ms-business-intelligence-ssis-controlflow-11/



Comentarios

Publicar un comentario

Entradas populares de este blog

Para que sirve la N en SQL Server

La N significa National language character set y lo que esto nos esta diciendo es que se trata de datos unicode. Esto significa que se están pasando datos del tipo NCHAR, NVARCHAR o NTEXT, que son distintos a los CHAR, VARHCAR o TEXT. Varchar equivale a Variable-Lenght Character String, es decir, una cadena de caracteres de tamaño variable. NVARCHAR, no es mas que un VARCHAR Unicode, es decir, un VARCHAR que soporta caracteres de 2 bytes, la idea de este tipo de datos es el poder soportar caracteres que no hacen parte del idioma ingles, como por ejemplo, querer guardar nombres en Ingles y en Japonés, o en Mandarín… En conclusión, la letra N que se antepone a las cadenas de caracteres, simplemente convierten la cadena en UNICODE (Estándar de la industria). Unicode permite garantizar el estándar de escritura, ya que en Unicode existe una representación numérica única para cada carácter. Ejemplo 01  : DECLARE @n NVARCHAR(10) SET @n = 'ā' PRINT @n SET @n = N 'ā...
Leer más

MYSQL : CURSORES

CURSORES EN MYSQL Ejemplo de un procedimiento almacenado con uso de cursor : DELIMITER // CREATE PROCEDURE mejor_vendedor(fecha_inicio DATE, fecha_final DATE) BEGIN -- Declaración de variables DECLARE ID_VENDEDOR INT; DECLARE ACUMULADO_VENTAS INT; DECLARE TEMPV INT DEFAULT 0; DECLARE TEMPID INT DEFAULT 0; -- Definición de la consulta /* No significa que el objeto cursor_edad vaya a guardar los datos de la consulta a la cual esta referenciando. Lo que hace es apuntar a la dirección de memoria del primer resultado de dicha consulta. Si tienes conocimientos en C++ se te hará mas fácil comprender esta interpretación. */ DECLARE mejor_vendedor_cursor CURSOR FOR SELECT V.IDVENDEDOR,SUM(DF.UNIDADES*DF.PRECIO) FROM VENDEDOR AS V INNER JOIN FACTURA AS F ON V.IDVENDEDOR = F.IDVENDEDOR  AND (F.FECHA BETWEEN fecha_inicio AND fecha_final) INNER JOIN DETALLEFACTURA AS DF ON F.IDFACTURA = DF.IDFACTURA GROUP BY V.IDVENDEDOR; -- Declaración de un manejador de error t...
Leer más

SQL SERVER : EJECUTAR UN PAQUETE SSIS

Existe 4 formas diferentes de ejecutar un paquete SSIS, las cuales van desde crear una aplicación en .NET hasta ejecutar procedimientos almacenados. A continuación veremos cuales son: Programando en .NET: Utilizando el espacio de nombres Microsoft.SqlServer.ManagedDTS , se puede programar una aplicación que ejecute un paquete de SSIS. El problema, para nosotros es que solamente se puede ejecutar paquetes de forma local, o sea, que estén en el mismo servidor donde se encuentre aplicación. Ejemplo: pueden ver un ejemplo aquí: http://msdn.microsoft.com/en-us/library/ms403355.aspx . Usar la utilidad DTExec.exe: Este es un programa que a través de línea de comando es posible ejecutar un paquete SSIS, pueden ver los distintos parámetros aquí: http://msdn.microsoft.com/en-us/library/ms162810.aspx . Igual que la forma anterior solamente se puede ejecutar de manera local. Usar SQL Agent: Usando el procedimiento almacenado “sp_start_job”, se puede ejecutar un job el cual puede ejecutar ...
Leer más