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:
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.
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.
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 segundo contenedor Foreach Loop llamado Bucle
Hojas como se muestra abajo:
Configure
el Origen de Excel para leer el
archivo de Excel apropiado y la hoja que actualmente está siendo enlazada.
En la
ficha Mappings.
Se ejecuta el paquete. Debajo
de la captura de pantalla se muestra la ejecución exitosa del paquete, en SSMS.
