使用 Foreach 循环容器循环遍历 Excel 文件和表Loop through Excel Files and Tables with a Foreach Loop Container

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory

本主题中的过程介绍如何使用具有相应枚举器的 Foreach 循环容器循环访问文件夹中的 Excel 工作簿或 Excel 工作簿中的表。The procedures in this topic describe how to loop through the Excel workbooks in a folder, or through the tables in an Excel workbook, by using the Foreach Loop container with the appropriate enumerator.

重要

有关连接到 Excel 文件的详细信息,以及从 Excel 文件加载数据或将数据加载到 Excel 文件的限制和已知问题,请参阅使用 SQL Server Integration Services (SSIS) 从 Excel 加载数据或将数据加载到 Excel 中For detailed info about connecting to Excel files, and about limitations and known issues for loading data from or to Excel files, see Load data from or to Excel with SQL Server Integration Services (SSIS).

使用 Foreach 文件枚举器循环遍历 Excel 文件To loop through Excel files by using the Foreach File enumerator

  1. 创建一个将在每次循环迭代中接收当前 Excel 路径和文件名的字符串变量。Create a string variable that will receive the current Excel path and file name on each iteration of the loop. 若要避免验证问题,请分配有效的 Excel 路径和文件名作为该变量的初始值。To avoid validation issues, assign a valid Excel path and file name as the initial value of the variable. (本过程后面显示的示例表达式将使用变量名 ExcelFile。)(The sample expression shown later in this procedure uses the variable name, ExcelFile.)

  2. (可选)创建另一个字符串变量,用于存放 Excel 连接字符串的扩展属性参数的值。Optionally, create another string variable that will hold the value for the Extended Properties argument of the Excel connection string. 此参数包含一系列值,这些值指定 Excel 版本并确定第一行是否包含列名称,以及是否使用导入模式。This argument contains a series of values that specify the Excel version and determine whether the first row contains column names, and whether import mode is used. (此过程随后显示的示例表达式将使用变量名 ExtProperties,其初始值为“Excel 12.0;HDR=Yes”。)(The sample expression shown later in this procedure uses the variable name ExtProperties, with an initial value of "Excel 12.0;HDR=Yes".)

    如果您未使用扩展属性参数的变量,必须手动将它添加到包含连接字符串的表达式。If you do not use a variable for the Extended Properties argument, then you must add it manually to the expression that contains the connection string.

  3. 将 Foreach 循环容器添加到 “控制流” 选项卡。有关如何配置 Foreach 循环容器的信息,请参阅 配置 Foreach 循环容器Add a Foreach Loop container to the Control Flow tab. For information about how to configure the Foreach Loop Container, see Configure a Foreach Loop Container.

  4. 在“Foreach 循环编辑器”的“集合”页上,选择“Foreach 文件”枚举器,并指定 Excel 工作簿所在的文件夹,然后指定文件筛选器(通常是 *.xlsx)。On the Collection page of the Foreach Loop Editor, select the Foreach File enumerator, specify the folder in which the Excel workbooks are located, and specify the file filter (ordinarily *.xlsx).

  5. 在“变量映射”页中,将索引 0 映射到用户定义字符串变量,该变量将在每个循环迭代中接收当前 Excel 路径和文件名。On the Variable Mapping page, map Index 0 to a user-defined string variable that will receive the current Excel path and file name on each iteration of the loop. (本过程后面显示的示例表达式将使用变量名 ExcelFile。)(The sample expression shown later in this procedure uses the variable name ExcelFile.)

  6. 关闭 “Foreach 循环编辑器”Close the Foreach Loop Editor.

  7. 按照 在包中添加、删除或共享连接管理器中所述,将 Excel 连接管理器添加到包。Add an Excel connection manager to the package as described in Add, Delete, or Share a Connection Manager in a Package. 为连接选择一个现有 Excel 工作簿文件以避免出现验证错误。Select an existing Excel workbook file for the connection to avoid validation errors.

    重要

    若要避免在对使用此 Excel 连接管理器的任务和数据流组件进行配置时出现验证错误,请在 “Excel 连接管理器编辑器” 中选择一个现有的 Excel 工作簿。To avoid validation errors as you configure tasks and data flow components that use this Excel connection manager, select an existing Excel workbook in the Excel Connection Manager Editor. 在您按照下列步骤为 ConnectionString 属性配置表达式以后,连接管理器在运行时将不使用此工作簿。The connection manager will not use this workbook at run time after you configure an expression for the ConnectionString property as described in the following steps. 在创建并配置包后,可在“属性”窗口中清除 ConnectionString 属性的值。After you create and configure the package, you can clear the value of the ConnectionString property in the Properties window. 但是,如果清除此值,要等到 Foreach 循环运行时 Excel 连接管理器的连接字符串属性才会有效。However, if you clear this value, the connection string property of the Excel connection manager is no longer valid until the Foreach Loop runs. 因此,在使用了连接管理器的任务中,必须将 DelayValidation 属性设置为 True 以避免出现验证错误。Therefore you must set the DelayValidation property to True on the tasks in which the connection manager is used, or on the package, to avoid validation errors.

    还必须使用 Excel 连接管理器的 False 属性的默认值 RetainSameConnectionYou must also use the default value of False for the RetainSameConnection property of the Excel connection manager. 如果将此值更改为 True,该循环的每次迭代将继续打开第一个 Excel 工作簿。If you change this value to True, each iteration of the loop will continue to open the first Excel workbook.

  8. 选择新的 Excel 连接管理器,并在“属性”窗口中单击 “表达式” 属性,然后单击省略号。Select the new Excel connection manager, click the Expressions property in the Properties window, and then click the ellipsis.

  9. “属性表达式编辑器” 中,选择 ConnectionString 属性,并单击省略号。In the Property Expressions Editor, select the ConnectionString property, and then click the ellipsis.

  10. 在表达式生成器中,输入以下表达式:In the Expression Builder, enter the following expression:

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

    注意使用转义符“\”来转义扩展属性参数的值前后所需的内部引号。Note the use of the escape character "\" to escape the inner quotation marks required around the value of the Extended Properties argument.

    扩展属性参数不是可选的。The Extended Properties argument is not optional. 如果未使用变量来包含其值,则必须手动将它添加到表达式中,如以下示例所示:If you do not use a variable to contain its value, then you must add it manually to the expression, as in the following example:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 12.0"  
    
  11. 在 Foreach 循环容器中创建任务,这些任务使用 Excel 连接管理器来在每个与指定的文件位置和模式匹配的 Excel 工作簿上执行相同的操作。Create tasks in the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel workbook that matches the specified file location and pattern.

使用 Foreach ADO.NET 架构行集枚举器循环遍历 Excel 表To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator

  1. 创建使用 Microsoft ACE OLE DB 访问接口连接 Excel 工作簿的 ADO.NET 连接管理器。Create an ADO.NET connection manager that uses the Microsoft ACE OLE DB Provider to connect to an Excel workbook. 在“连接管理器”对话框的“所有”页上,确保输入 Excel 版本,在本例中,Excel 12.0 作为“Extended Properties”属性的值。On the All page of the Connection Manager dialog box, make sure that you enter the Excel version - in this case, Excel 12.0 - as the value of the Extended Properties property. 有关详细信息,请参阅 在包中添加、删除或共享连接管理器For more information, see Add, Delete, or Share a Connection Manager in a Package.

  2. 创建一个字符串变量,用于在每次循环迭代中接收当前表的名称。Create a string variable that will receive the name of the current table on each iteration of the loop.

  3. 将 Foreach 循环容器添加到 “控制流” 选项卡。有关如何配置 Foreach 循环容器的信息,请参阅 配置 Foreach 循环容器Add a Foreach Loop container to the Control Flow tab. For information about how to configure the Foreach Loop container, see Configure a Foreach Loop Container.

  4. “Foreach 循环编辑器”“集合” 页上,选择 Foreach ADO.NET 架构行级枚举器。On the Collection page of the Foreach Loop Editor, select the Foreach ADO.NET Schema Rowset enumerator.

  5. 对于 “连接” 的值,请选择前面创建的 ADO.NET 连接管理器。As the value of Connection, select the ADO.NET connection manager that you created previously.

  6. 对于 “架构” 的值,选择“表”。As the value of Schema, select Tables.

    备注

    Excel 工作簿中的表列表同时包括工作表(具有 $ 后缀)和指定范围。The list of tables in an Excel workbook includes both worksheets (which have the $ suffix) and named ranges. 如果要从列表中只筛选出工作表或指定范围,则必须在脚本任务中编写自定义代码来实现这一点。If you have to filter the list for only worksheets or only named ranges, you may have to write custom code in a Script task for this purpose. 有关详细信息,请参阅 使用脚本任务使用的 Excel 文件For more information, see Working with Excel Files with the Script Task.

  7. “变量映射” 页上,将索引 2 映射到以前创建的字符串变量,以存放当前表的名称。On the Variable Mappings page, map Index 2 to the string variable created earlier to hold the name of the current table.

  8. 关闭 “Foreach 循环编辑器”Close the Foreach Loop Editor.

  9. 在 Foreach 循环容器中创建任务,这些任务使用 Excel 连接管理器对指定工作簿中的每个 Excel 表执行相同的操作。Create tasks in the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel table in the specified workbook. 如果你使用脚本任务来检查枚举表名或处理每个表,请记住将字符串变量添加到脚本任务的 ReadOnlyVariables 属性。If you use a Script Task to examine the enumerated table name or to work with each table, remember to add the string variable to the ReadOnlyVariables property of the Script task.

另请参阅See Also

使用 SQL Server Integration Services (SSIS) 从 Excel 加载数据或将数据加载到 Excel 中Load data from or to Excel with SQL Server Integration Services (SSIS)
配置 Foreach 循环容器 Configure a Foreach Loop Container
添加或更改属性表达式 Add or Change a Property Expression
Excel 连接管理器 Excel Connection Manager
Excel 源 Excel Source
Excel 目标 Excel Destination
使用脚本任务处理 Excel 文件Working with Excel Files with the Script Task