Cargar Archivos Excel a una base de datos utilizando SSIS

Este Post es por una consulta, sobre este tema. Una posible forma de hacerlo se basa en la suposición de que no habrá ninguna hoja en blanco en los archivos de Excel y también todas las hojas tienen la misma estructura exacta. Además, bajo el supuesto de que la extensión del archivo es sólo .xlsx.

El ejemplo se ha creado usando SSIS 2014 y Excel 2010. La carpeta de trabajo para este ejemplo es C:\SSIS\Data Flow\Archivos\.

En la ruta de la carpeta C:\SSIS\Data Flow\Archivos\, crear dos archivos Excel 2010 llamados Ubigeo01.xlsx con dos hojas y Ubigeo02.xlsx con dos hojas.

La Hoja1 de Ubigeo01.xlsx contiene los siguientes datos:

 

La Hoja2 de Ubigeo01.xlsx contiene los siguientes datos:

 

La Hoja1 de Ubigeo02.xlsx contiene los siguientes datos:

La Hoja2 de Ubigeo01.xlsx contiene los siguientes datos:

 

Creamos en SSMS una base de datos o usamos alguna BD disponible. Luego creamos una tabla llamada Destination.

CREATE TABLE Destination

(

    Departamento nvarchar(255) NULL,

    Provincia nvarchar(255) NULL,

    Distrito nvarchar(255) NULL,

    RutaArchivo nvarchar(255) NULL,

    NombreHoja nvarchar(255) NULL

);

GO

Esta tabla está actualmente vacía.

Creamos un nuevo proyecto SSIS, renombramos el paquete SSIS y en el paquete, creamos las siguientes 4 variables. RutaCarpeta contendrá la carpeta donde se almacenan los archivos de Excel. PatronArchivo contendrá la extensión de los archivos que se enlazan desde ahí y este ejemplo sólo funciona para .xlsx. RutaArchivo asignará un valor para el contenedor Foreach Loop, pero necesitamos una ruta válida para empezar en tiempo de diseño y actualmente rellenado con la ruta C:\SSIS\Data Flow\Archivos\Ubigeo01.xlsx del primer archivo de Excel. NombreHoja contendrá el nombre real de la hoja, pero tenemos que llenar con el valor inicial Hoja1$ para evitar errores de tiempo de diseño.

En el administrador de conexión del paquete, creamos una conexión ADO.NET con la siguiente configuración y la nombramos como EsquemaExcel.

Seleccionar el proveedor Microsoft Office 12.0 Access Database Engine OLE DB Provider bajo .Net Providers for OleDb. Proporcionar la ruta del archivo C:\SSIS\Data Flow\Archivos\Ubigeo01.xlsx.

Haga clic en la sección All en el lado izquierdo y establezca la propiedad Extended Properties a Excel 12.0 para indicar la versión de Excel. Aquí, en este caso 12.0 denota Excel 2007. Haga clic en la conexión de prueba para asegurarse de que la conexión se realiza correctamente.

 

Creamos un administrador de conexión de Excel y lo llamamos Excel como se muestra a continuación.

 

Crear una conexión OLE DB de SQL Server denominada SQLServer. Por lo tanto, debemos tener tres conexiones en el paquete como se muestra a continuación.

 

Necesitamos hacer los siguientes cambios en la cadena de conexión para que el archivo de Excel sea cambiado dinámicamente a medida que los archivos sean enlazados.

Sobre la conexión EsquemaExcel, configure la expresión ServerName para utilizar la variable RutaArchivo. Haga clic en el botón de puntos suspensivos para configurar la expresión.

 

Del mismo modo en la conexión Excel, configure la expresión ServerName para utilizar la variable RutaArchivo. Haga clic en el botón de puntos suspensivos para configurar la expresión.

 

En el Control Flow, coloque dos contenedores Foreach Loop uno dentro del otro. El primer contenedor Foreach Loop llamado Bucle Archivos hará un bucle a través de los archivos. El segundo contenedor Foreach Loop, llamado Bucle Hojas, hará un bucle a través de las hojas dentro del contenedor. En el interior del contenedor Foreach Loop, coloque una tarea Data Flow que leerá los archivos de Excel y cargará los datos en SQL.

Configure el primer contenedor Foreach Loop llamado Bucle Archivos como se muestra abajo:

En la ficha Variable Mappings.

 

Configure el segundo contenedor Foreach Loop llamado Bucle Hojas como se muestra abajo:

En la ficha Variable Mappings.

Dentro de la tarea Data Flow, colocar un Origen de Excel, una Columna Derivada y un Destino de OLE DB, como se muestra a continuación:

 

Configure el Origen de Excel para leer el archivo de Excel apropiado y la hoja que actualmente está siendo enlazada.

En la ficha Columns.

Configure la Columna Derivada para crear nuevas columnas para la ruta de archivo y el nombre de hoja.

Configure el destino OLE DB para insertar los datos en la tabla SQL.

 

En la ficha Mappings.

Se ejecuta el paquete. Debajo de la captura de pantalla se muestra la ejecución exitosa del paquete, en SSMS.