Recorrer archivos y tablas de Excel con un contenedor de bucle Foreach

Se aplica a:SQL Server SSIS Integration Runtime en Azure Data Factory

Los procedimientos de este tema explican cómo crear bucles entre libros de Excel en una carpeta o entre tablas en un libro de Excel, mediante el contenedor de bucles Foreach con el enumerador correspondiente.

Importante

Para obtener información detallada sobre cómo conectarse a archivos de Excel y sobre las limitaciones y problemas conocidos a la hora de cargar datos de o a archivos de Excel, vea Cargar datos de o a Excel con SQL Server Integration Services (SSIS).

Para recorrer archivos de Excel mediante el enumerador Foreach File

  1. Cree una variable de cadena que recibirá la ruta de acceso y nombre de archivo de Excel actuales en cada iteración del bucle. Para evitar problemas de validación, asigne una ruta de acceso y un nombre de archivo de Excel válidos como valor inicial de la variable (la expresión de ejemplo que se muestra más adelante en este procedimiento utiliza el nombre de variable ExcelFile).

  2. Opcionalmente, cree otra variable de tipo cadena que contendrá el valor del argumento "Propiedades extendidas" de la cadena de conexión de Excel. Este argumento contiene una serie de valores que especifican la versión de Excel y determinan si la primera fila contiene nombres de columna, y si se utiliza un modo de importación (la expresión de ejemplo que se muestra más adelante en este procedimiento utiliza el nombre de variable ExtProperties, con un valor inicial de "Excel 12.0;HDR=Yes").

    Si no usa una variable para el argumento Propiedades extendidas, debe agregarlo manualmente a la expresión que contiene la cadena de conexión.

  3. Agregue un contenedor de bucles Foreach a la pestaña Flujo de control . Para obtener más información sobre cómo configurar el contenedor de bucles Foreach, vea Configurar un contenedor de bucles Foreach.

  4. En la página Colección del Editor de bucles Foreach, seleccione el enumerador de archivos para Foreach, especifique la carpeta en la que se encuentran los libros de Excel e indique el filtro de archivos (generalmente *.xlsx).

  5. En la página Asignaciones de variables , asigne Índice 0 a una variable de cadena definida por el usuario que recibirá la ruta de acceso y el nombre de archivo de Excel actuales en cada iteración del bucle. (La expresión de ejemplo que se muestra más adelante en este procedimiento utiliza el nombre de variable ExcelFile.)

  6. Cierre el Editor de bucles Foreach.

  7. Agregue un administrador de conexiones con Excel al paquete como se describe en Agregar, eliminar o compartir un administrador de conexiones en un paquete. Seleccione un archivo de libro de Excel existente para la conexión para evitar errores de validación.

    Importante

    Para evitar que se produzcan errores de validación al configurar tareas y componentes de flujo de datos que utilizan este administrador de conexiones con Excel, seleccione un libro de Excel existente en el Editor del Administrador de conexiones con Excel. El administrador de conexiones no utilizará ese libro en tiempo de ejecución después de configurar una expresión para la propiedad ConnectionString como se describe en los pasos siguientes. Tras crear y configurar el paquete, puede borrar el valor de la propiedad ConnectionString en la ventana Propiedades. Sin embargo, si se borra este valor, la propiedad de la cadena de conexión del administrador de conexiones de Excel deja de ser válida hasta que se ejecute el bucle Foreach. Por lo tanto, debe establecer la propiedad DelayValidation en True en las tareas en las que se utiliza el administrador de conexiones, o bien en el paquete, para evitar errores de validación.

    También debe utilizar el valor predeterminado de False para la propiedad RetainSameConnection del administrador de conexiones de Excel. Si cambia este valor a True, cada iteración del bucle continuará abriendo el primer libro de Excel.

  8. Seleccione el nuevo administrador de conexiones de Excel, haga clic en la propiedad Expresiones en la ventana Propiedades y luego haga clic en los puntos suspensivos.

  9. En el Editor de expresiones de propiedad, seleccione la propiedad ConnectionString y haga clic en los puntos suspensivos.

  10. En el Generador de expresiones, escriba la siguiente expresión:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""

    Observe el uso del carácter de escape "\" para las comillas internas necesarias en el valor del argumento Propiedades extendidas.

    El argumento Propiedades extendidas no es opcional. Si no usa una variable para contener su valor, debe agregarlo manualmente a la expresión, como en el ejemplo siguiente:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 12.0"

  11. Cree tareas en el contenedor Bucle Foreach que utilicen el administrador de conexiones de Excel para realizar las mismas operaciones en cada libro de trabajo de Excel que coincida con la ubicación de archivo y el patrón especificados.

Para crear un bucle entre tablas de Excel con el enumerador de conjunto de filas del esquema para Foreach de ADO.NET

  1. Cree un administrador de conexiones ADO.NET que use el Proveedor OLE DB de Microsoft ACE para conectarse a un libro de trabajo de Excel. En la página Todos del cuadro de diálogo Administrador de conexiones, asegúrese de especificar la versión de Excel (en este caso, Excel 12.0) como valor de la propiedad Propiedades extendidas. Para obtener más información, consulte agregar, eliminar o compartir un administrador de conexiones en un paquete.

  2. Cree una variable de cadena que recibirá el nombre de la tabla actual en cada iteración del bucle.

  3. Agregue un contenedor de bucles Foreach a la pestaña Flujo de control . Para obtener más información sobre cómo configurar el contenedor de bucles Foreach, vea Configurar un contenedor de bucles Foreach.

  4. En la página Colección del Editor de bucles Foreach, seleccione el enumerador de conjunto de filas del esquema para Foreach de ADO.NET.

  5. Como valor de Conexión, seleccione el administrador de conexiones de ADO.NET que creó previamente.

  6. Como valor de Esquema, seleccione Tablas.

    Nota

    La lista de tablas de un libro de Excel incluye tanto hojas (que tienen el sufijo $) como rangos con nombre. Si necesita filtrar la lista para mostrar solo las hojas de cálculo o solo los rangos con nombre, puede que tenga que escribir código personalizado en una tarea de script para ello. Para obtener más información, consulte trabajar con archivos de Excel con la tarea de secuencia de comandos.

  7. En la página Asignaciones de variables, asigne el índice 2 a la variable de tipo cadena que creó anteriormente para almacenar el nombre de la tabla actual.

  8. Cierre el Editor de bucles Foreach.

  9. Cree tareas en el contenedor de bucles Foreach que utilicen el administrador de conexiones con Excel para realizar las mismas operaciones en cada tabla de Excel en el libro especificado. Si usa una tarea Script para examinar el nombre de la tabla enumerada o para trabajar con cada tabla, recuerde agregar la variable de cadena a la propiedad ReadOnlyVariables de la tarea Script.

Contenido relacionado

  • Last updated on