ssis foreach 参数

Foreach 循环本身不直接使用“参数”这个词，但它通过 变量 来实现对当前遍历项的访问和传递，理解这一点是掌握 Foreach 的关键。

核心概念：Foreach 循环如何工作？

Foreach 循环容器的工作流程如下：

1. 枚举器：你需要选择一个枚举器，枚举器的任务是获取一个集合,文件枚举器会获取一个文件夹中所有符合扩展名的文件列表。
2. 映射变量：你需要将集合中的每一项（一个文件的完整路径）映射到一个或多个 SSIS 变量。
3. 执行任务：循环开始，它会从集合中取出第一项，将其值赋给你映射的变量，然后执行循环内部的容器（如 Execute SQL Task, Data Flow Task 等）。
4. 重复：执行完毕后，循环会回到顶部，取出集合中的第二项，再次赋值给变量，并执行内部任务，这个过程会一直持续,直到集合中的所有项都被处理完毕。

Foreach 循环的配置

在 SSIS 设计器中，右键点击 Foreach Loop Container，选择 Edit...,你会看到以下主要配置选项：

选择枚举器

这是最关键的一步，决定了你将要遍历什么,常见的枚举器有：

配置枚举器

选择枚举器后，下方会出现相应的配置选项，以最常用的 Foreach File Enumerator 为例：

• Folder: 要遍历的文件夹路径,可以使用表达式动态指定。
• Files: 文件名/通配符。*.txt, Data_*.csv。
• Retrieve file name: 非常重要！ 这里决定你将文件名的哪部分映射给变量。
  • Name with path: 完整路径，如 C:\Data\sales_2025.csv。
  • Name: 仅文件名，如 sales_2025.csv。
  • Extension: 仅扩展名，如 .csv。
  • Directory: 仅文件夹路径，如 C:\Data。

变量映射

这是“参数”传递的核心。

• 点击 Variable Mappings 页面。
• 在 Variable 列中，选择一个你已创建好的 SSIS 变量，这个变量的数据类型必须与枚举器返回的项相匹配（对于文件路径，通常是 String）。
• 在 Index 列中，指定索引，对于大多数枚举器（如文件、ADO），索引 0 就代表集合中的当前项，对于 Foreach Nodelist，你可能需要映射多个变量（如节点名、节点值等），这时会用到索引 0, 1, 2 等。

示例：

1. 创建一个变量 User::FilePath，类型为 String。
2. 在 Variable Mappings 中，将 User::FilePath 映射到索引 0。
3. 在 Foreach File Enumerator 中，选择 Retrieve file name 为 Name with path。
4. 在循环内部的任何任务中，你都可以使用 @[User::FilePath] 来获取当前正在处理的文件的完整路径。

在任务中使用 Foreach 变量（即“参数”）

一旦你将集合项映射到了变量,就可以在循环内的各种任务中使用它了。

场景1：在 Execute SQL Task 中使用

假设你有一个 Foreach ADO Enumerator，它遍历一个包含客户ID的列表,你想要为每个客户执行一个存储过程。

1. 变量:

   • User::CustomerID (类型为 Int32)
   • User::SqlStatement (类型为 String)

2. Foreach Loop 配置:

   • 枚举器: Foreach ADO Enumerator
   • ADO Object Source Variable: 指向你的 Recordset 变量 (User::RS_CustomerIDs)
   • Variable Mappings: 将 User::CustomerID 映射到索引 0

3. Execute SQL Task 配置 (在循环内部):

   • General: 设置为 SQL Statement。
   • SQLStatement: 使用表达式动态构建 SQL 语句。
     • 在属性窗口中，找到 Expression 属性，点击省略号 。
     • 输入表达式："EXEC usp_ProcessCustomer " + (DT_STR, 10, 1252) @[User::CustomerID]
       • DT_STR, 10, 1252 是一个类型转换，确保 Int32 类型的变量能被正确地拼接到字符串中，1252 通常是代码页。
   • Parameter Mapping: 如果你的 SQL 语句使用参数化查询（ 或 @param），这里就需要配置。
     • Variable Name: User::CustomerID
     • Direction: Input
     • Data Type: LONG (对应 Int32)
     • Parameter Name: 0 (或 @CustomerID,取决于你的提供方)

场景2：在 Data Flow Task 中使用

这是最常见的场景之一：遍历文件夹中的文件,并将每个文件导入到数据库中。

1. 变量:

   • User::FilePath (类型为 String)

2. Foreach Loop 配置:

   • 枚举器: Foreach File Enumerator
   • 配置文件夹和文件通配符（如 *.csv）。
   • Variable Mappings: 将 User::FilePath 映射到索引 0，并选择 Name with path。

3. Data Flow Task 配置 (在循环内部):

   • 源: 使用 Flat File Source。
     • 在 Flat File Connection Manager Editor 中，不要使用一个固定的文件连接管理器。
     • 在属性窗口中，找到 Connection 属性，点击 ，选择 New connection from variable。
     • 在下拉菜单中选择 User::FilePath。
     • 这样，每次循环时，Flat File Source 都会读取 @[User::FilePath] 指向的那个新文件。
   • 目标: 使用你的 OLE DB Destination 或其他目标组件,将数据导入数据库。

进阶技巧：使用表达式动态配置

Foreach 循环本身以及它内部的任务，都可以使用 SSIS 表达式来实现高度的动态性。

• 动态文件夹路径:

  • 假设你的文件夹路径存储在变量 User::RootFolder 中。
  • 在 Foreach Loop Container 的属性窗口中，找到 Directory 属性，点击 。
  • 输入表达式：@[User::RootFolder] + "\\InputFiles\\"，这样你就可以通过改变 User::RootFolder 的值来控制遍历的文件夹,而无需修改包。

• 动态文件名:

  • 在 Foreach File Enumerator 的 Files 属性中，你可以使用表达式来动态构建通配符，"Data_" + (DT_WSTR, 4) DATEPART("yyyy", GETDATE()) + "*.csv"。

总结与最佳实践

1. 明确你的集合来源: 首先确定你要遍历的是文件、数据库表还是其他什么,然后选择正确的枚举器。
2. 规划好变量: 在配置 Foreach 循环之前，先创建好你需要的变量,并设置好正确的数据类型。
3. 善用变量映射: 这是连接枚举器和内部任务的桥梁，你是在将集合中的“当前项”赋值给一个变量。
4. 区分“变量”和“参数”: 在 SSIS 的语境下，我们通常说“传递变量值”而不是“传递参数”，但概念上,这个变量值就起到了参数的作用。
5. 使用表达式增强灵活性: 不要把路径、文件名等写死,通过变量和表达式可以让你的包更具复用性和可维护性。
6. 调试: 在开发过程中，可以在循环内部添加一个 Script Task 或 Script Component，使用 MessageBox.Show(Dts.Variables["User::FilePath"].Value.ToString()) 来查看当前处理的项是否正确，这是调试 Foreach 循环最简单有效的方法。

希望这份详细的解释能帮助你完全掌握 SSIS Foreach 循环和参数（变量）传递！