[分享]微软BI专题-SQL Server BI Development Studio使用技巧系列（一）

 

——Foreach循环容器使用方法

 

     Foreach循环容器定义包中的重复控制流。循环的实现与编程语言中的Foreach循环结构类似。在包中，通过使用Foreach枚举器启用循环。 Foreach循环容器将重复指定枚举器的每个成员的控制流。本期文章中，我们介绍其中两种常用的枚举器使用方法：Foreach文件枚举器、 ForeachADO枚举器。

 

      Foreach循环容器定义包中的重复控制流。循环的实现与编程语言中的Foreach循环结构类似。在包中，通过使用Foreach枚举器启用循环。Foreach循环容器将重复指定枚举器的每个成员的控制流。

SQL Server 2005 Integration Services (SSIS) 提供了7种枚举器类型：

1.     ForeachADO 枚举器，用于枚举表中的行。例如，可以获取ADO记录集中的行。

2.     ForeachADO.NET架构行集枚举器，用于枚举有关数据源的架构信息。例如，可枚举并获取一组AdventureWorks SQL Server数据库中的表。

3.     Foreach文件枚举器，用于枚举文件夹中的文件。该枚举器可遍历子文件夹。例如，可读取 Windows 文件夹中所有具有 *.log文件扩展名的文件。

4.     Foreach源变量枚举器，用于枚举指定变量包含的可枚举对象。例如，变量包含运行时枚举的查询的结果。

5.     Foreach项枚举器，用于枚举集合项。例如，可枚举 Excel 电子表格中的行和列。

6.     ForeachNodelist枚举器，用于枚举XML Path 语言 (XPath)表达式的结果集。例如，此表达式可枚举并获取古代的所有作者：/authors/author[@period='classical']。

7.     ForeachSMO枚举器，用于枚举SQL Server管理对象(SMO)对象。例如，可枚举并获取SQL Server数据库中的视图列表。

今天，我们介绍其中两种常用的枚举器使用方法：Foreach文件枚举器、ForeachADO 枚举器。

 

      Foreach文件枚举器。

      该枚举器对指定目录下的文件进行逐一读取，然后在Foreach容器中循环使用。

例如：我们要对目录c:/temp/下的所有.txt文件进行逐一读取处理。使用Foreach文件枚举器关键操作步骤如下：

一、使用Foreach容器：在Business Intelligence Development Studio中，创建Integration Service项目，并从“工具箱”中，将“Foreach循环容器”拖到“控制流”选项卡的设计图面上。右键单击新添加的“Foreach循环容器”，选择“编辑”。

二、 设置循环类型：在“集合”页上，选择“Foreach文件枚举器”。在“枚举器配置”组中，单击“浏览”。找到前面创建的示例数据文件夹C:/temp，并将处理文件的类型设置为*.txt。

三、定义枚举器变量：在“变量映射”页的“变量”列中，新建变量varFileName，用于获取Foreach每次循环所读取的文件名，如图一

四、在Foreach循环容器中添加数据流任务，读取.txt文件。

五、为数据流任务添加平面文件源组件，并做初始设置。如：平面文件的行分隔符、列分割符、是否有标题行等等；这个时候的文件名可以选取任何一个与C:/temp目录下的.txt文件格式相同的文件。如图2

六、 配置平面文件连接管理器，以使用枚举器变量：在“连接管理器”窗格中，右键单击Flat File Data Source选择属性。在“属性表达式编辑器”对话框的“属性”列中，键入或选择ConnectionString。将变量 User::varFileName 赋给ConnectionString。如图3

由此，我们通过Foreach的文件枚举器，实现了对目录c:/temp/下的所有.txt文件进行循环读取处理的目的。

      ForeachADO 枚举器

      该枚举器对指定表中的行进行逐一读取，然后在Foreach容器中循环使用。

例如：我们有一张表不规范的员工信息表：Employee和一张部门设置表DimEmployeeGroup；我们希望经过ETL转换，得到标准的员工信息表DimEployee，如图4。

我们就可以采到Foreach容器的ForeachADO枚举器，实现上述需求，其关键步骤如下：

一、    在项目中添加3个包级变量：分别为：Object 型变量Resultset；INT 型变量 id；String 型变量name；

二、    添加执行SQL任务get_family_name，查询EmployeeGroup表中的数据存放到Resultset变量中。如图5

三、    添加Foreach循环容器：Foreachloop Container并配置”集合”选项，在”ADO对象源变量(v)”处选择Resultset变量,作为循环所依据的表集。 点击”变量映射”选项，选择变量id索引0、name索引1，接收每次循环的结果。如图6

四、    向容器中添加执行SQL任务set_dimemployee：通过变量映射，获取循环所得的结果，并在SQL命令中使用该变量，将标准数据添加到DimEmployee表中。

 

由此，我们就通过Foreach容器的ForeachADO枚举器，实现了图4中的需求，参照DimEmployeeGroup表，将不规范的Employee信息，导入到了标准的DimEmployee表中。
……