SSIS关键元素的使用(1)

1、.1 SSIS关键元素的使用 第三讲（2） .2 在前面创建包的过程中，用到了任务、容器、数据源和目 标，以及转换等SSIS元素。其中，任务有数据流任务和文 件系统任务，它们分别完成数据流控制和将文件复制到备 份文件夹；容器组件则用到了序列容器，作用是把数据流 和文件系统任务打包成一个整体；数据源主要是OLE DB 源，即数据流中的第一个组件，其作用是从 AdventureWorks数据库的“TransactionHistoryArchive”表中 加载归档销售事务数据。firstSSIS包中使用了4个转换组件 来完成数据流中数据的转换，第1个是名为“计算每一项 的总成本”的派生列转换，它通过

2、将每个销售事务的成本 与数量相乘并将其结果存储在创建的新列中；第2个是聚 合转换（汇总数量和成本），它按“ProductID”列对数据 进行分组，并且对于每一个“ProductID”都计算其 “Quantity”列的和作为“QuantitySum”列，同时计算 “LineItemTotalCost”列的和作为“TotalCostByID”列； .3 第3个转换也是一个派生列转换组件，其作用是计算平均 成本并且存储在一个新列中；最后，系统还用到了排序转 换，将计算结果按“ProductID”列进行排序。另外还使用 到了平面文件目标和3个连接管理器。 由此可见，只有这些组件的协同运作，一个包才能顺

3、利地 进行，同时，也只有每个组件都在理解的基础上应用，才 能创建一个完成需要功能的包。 .4 一、包 包（package）是最重要的 Integration Services对象。包 是一个有组织的集合，其中 可包括连接、控制流元素、 数据流元素、事件处理程序、 变量和配置。包有3种创建方 式，包括使用SSIS导入和导 出向导、使用SSIS设计器及 编程实现。 包对象的主要功能是把其他 SSIS对象组合成一个整体。 首次创建包后，包是一个空 对象，不能实现任何功能。 若要向包添加功能，就需要 向包添加对象，如控制流、 数据流及相关的元素。 包 包 控制流 任务 源 转换 目标 数据流任务 .5

4、 二、容器 容器、任务及优先约束都属于控制流元素。其 中容器提供包中的结构和服务，任务提供功能， 优先约束将任务和容器连接为已排序的控制流。 SSIS容器主要包括3类容器： Foreach循环容器：枚举一个集合，并对该集合的 每个成员重复其控制流。 For循环容器：用于重复其控制流，直到指定表达 式的计算结果为False为止。 序列容器：在容器内定义控制流的子集，并将任务 和容器作为一个单元来管理。 .6 1、Foreach循环容器 Foreach循环容器定义包中的重复控制流。循环的实 现与编程语言中的FOREACH循环结构类似，包将 会重复执行循环容器中枚举器的每个成员。 该示例的目的是把一

5、个文件夹中的特定文件复制到 备份文件夹中。 （1）新建Integration Services 项目，名称为 “foreachdemo”。 （2）在工具箱中选择“控制流项”标签中的“文件 系统任务”组件。设置此文件系统任务的类型为 “复制文件”。源连接指向任何一个文件。目标连 接指向要复制到的目标文件夹。现在这个文件系统 任务只能完成单个文件的复制。 .7 .8 （3）添加“Foreach循环容器”组件，在弹出的快捷菜单中选择 “编辑”命令。 （4）选择“集合”选项，在“枚举器配置”选项组中，单击“浏 览”按钮，设置成包含需要备份文件的文件夹，为了发挥 Foreach循环容器的枚举功能，此文件

6、夹中应该有一系列的文件。 （5）在“文件”文本框中，键入需要复制的文件属性，例如若要 复制全部的文本文件，可以输入“*.txt”。 （6）设置Foreach循环容器所枚举的对象与任务组件之间的关联。 选择“变量映射”选项，添加新的变量copyfile，通过此变量实 现容器所枚举的所有对象与其他SSIS任务组件的关联。 （7）把“文件系统任务”组件拖动到Foreach循环容器中。 （8）设置source连接属性窗口中的“表达式”，在“属性表达式 编辑器”窗口中属性列中选择“ConnectionString”，在弹出的 “表达式生成器”窗口中将第5步设置的变量拖动到“表达式” 窗口中。 （9）返回

7、到SSIS设计器，执行此包。源文件夹中的所有文件已经 备份到目标文件夹中了。 .9 第4步图 .10 第5步图 .11 第7步图 .12 Foreach循环容器 回顾本例的制作过程，实现文件批量复制 的最关键的地方在于将平面文件连接管理 器的ConnectionString属性修改为在Foreach 循环容器配置时设置的用户定义的变量 copyfile。运行时，Foreach循环容器的每次 迭代都将动态更新copyfile变量，这样会使 连接管理器连接到不同的平面文件，从而 逐一处理源文件夹中的文件。 .13 2、For循环容器 For循环容器定义包中的重复控制流。循环 每次重复时，For循环

8、容器都计算一个表示 式并重复运行其工作流，直到表达式计算 结果为false。 For循环容器需要三个定义的要素： 为循环计数器赋值的可选初始化表达式 包含用于测试循环应停止还是继续的表达式 递增或递减循环计数器的可选迭代表达式 .14 2、For循环容器 1、创建一个变量， 选择“SSIS” “变量”。 2、如图设置For循环 容器。 3、在容器中添加一 个需要重复执行的 任务。 .15 3、序列容器 当控制流过于庞大不容易管理时，可以把 完成流某一项功能的操作作为一个大控制 流的一部分，这就需要使用序列容器。序 列容器将包分组到多个单独的控制流中。 使用序列容器辅助控制流有许多好处，比 如可

9、以禁用任务组方便调试。 .16 三、任务 任务是一些控制流元素，它定义包含控制 流中执行的工作单元。SQL Server Integration Service包由一个或多个任务组成。 如果包中包含多个任务，则按照优先约束 在控制流中进行连接和排序。 .17 1、大容量插入任务 大容量插入任务为文本文件的大量数据复制到SQL Server表或视图提 供了最为快速的方法。 .18 2、执行SQL任务 执行SQL任务可以从包中运行SQL语句或存 储过程。此任务可以包含单个SQL语句，也 可以包含顺序运行的多个SQL语句。 执行SQL任务可以与Foreach循环容器和For 循环容器一起组合使用，以

10、运行多个SQL语 句或重复运行执行SQL任务。 .19 四、创建一个完整的SSIS包 该实例的背景是基于Adventure Works Cycle公司的，该公司 新增了5个销售区域，以前这5个区域的销售数据没有汇总 到数据库中，现在需要用到这些数据，于是公司让这5个 区域的主管把客户信息全部导入到一个文本文件中，命名 为customers.txt。 几个区域的数据完全混合在一起，而且某些邮政编码丢失 前导零导致位数不够，某些数据销售区域数值无效。现在 的任务就是要通过SSIS的数据提取、转换和加载功能检测 并更正customers.txt不正确的邮政编码，将具有无效销售区 域的数据行写入一个特

11、定的文件，并将有效数据行分区域 导入到 AdventureWorks 数据库中的五个表。 .20 基本解决思路是，先创建5个SQL语句来创建5个 表，每一个表对应一个有效区域，使用Foreach循 环容器遍历保存在文件夹中的文本文件中的SQL 语句。 Customers.txt保存的数据使用条件分割转换分别导 入到对应的5个数据表中，不正确的数据导入到一 个新的文件中保存。 实例中将用到各种任务、容器、源、转换、目标 和连接管理器。通过本例的学习，我们会对用 SSIS完成数据的ETL操作有一个全方位的认识。 .21 （一）生成SSIS解决方案 1、创建Integeration Services

12、项目，项目名称 为alldemo。 2、在SSIS设计界面窗口， 把SSIS包文件夹下的 Package.dtsx命名为 alldemo.dtsx。 .22 （二）设计控制流 1、将Foreach循环容器组件拖到控制流编辑器 中，命名为“Foreach循环运行SQL语句” 。循环编辑器的“集合”页按下图所示进 行设置，注意要把文件夹选项定位到此项 目中包含创建表的SQL文件的文件夹中。 .23 .24 2、切换到变量映射页，在变量下拉列表中选择“新 建变量”选项，进行如图所示的设置。这一步创 建了一个包级别的用户变量vfileName。 .25 3、在连接管理其中新建一个文件连接，命名为 Cr

13、eateTableSQL。注意这里先把文件名指向其中一个创建 表的SQL文件，后面会把变量参数传递给这个文件连接。 4、修改这个文件连接CreateTableSQL的属性，将其“表达式 ”属性中的ConnectionString修改为vfileName变量。 .26 5、按照需求，需要在循环中加入一个创建SQL的组 件，故在工具箱中选择“执行SQL任务”组件拖 动到Foreach循环容器中，命名为“创建表”。然 后按照如下图所示进行设置。其中“执行SQL任 务编辑器”中的常规页面的Connection属性要创建 一个连接指向AdventureWorks数据库； SQLSourceType为选择

14、“文件连接”； FileConnection指向CreateTableSQL文件连接。 .27 .28 6、选择“数据流任务”组件，拖动到控制流 设计器重，命名为“数据清洗和加载”， 同时把优先约束从Foreach循环指向这个数 据流组件。 .29 （三）设计数据连接 此项目最核心的任务是对文本文件数据源的处理 ，因此还要创建一个连接指向Customers.txt。 1、在连接管理器中创建一个平面文件连接。由于 Customers.txt文件数据第一行是字段名，要在编辑 器中选中“在第一个数据行中显示列名称”选项 ，如下图所示。 .30 .31 2、在“平面连接管理器”窗口中切换到“高级”页

15、面配置各列的属性。这里可以设置各列的名称和 长度等属性。此处应该按照前面创建表的SQL语 句设置数据的字段类型，否则在后面数据导入的 时候会由于字段类型的不匹配而发生错误。 另外，因为需要保存无效的客户数据，还要创建 一个连接指向保存这些数据的文本文件，将此连 接命名为CustomersWithInvalidTerrritoryID。 .32 .33 .34 （四）设计数据流 双击控制流中的“数据清洗和加载”数据流任务 组件，切 换到数据流编辑器。 1、把“平面文件源”组件拖动到数据流编辑器中，改名为 “数据提取”，按下图所示进行设置。 .35 2、选择“条件性拆分”组件，命名为“按照 Ter

16、ritoryID拆分数据”，并将数据流从上一步创建 的“数据提取”文件源中指向这个条件性拆分组 件。 3、双击条件性拆分组件，在“条件性拆分转换编辑 器”中，展开左上角的列对象，把Territory字段拖 动到网格中的“条件”列，同时编辑“条件”列 表达式为“Territory=1”。为了使操作的结果 更直观，可以把输出名称改为“区域1”。其他的 条件列可以进行相应的设置。默认输出名称改为 “无效区域”。最终结果如下图所示。 .36 .37 4、拖动4个“OLE DB目标”组件到数据流编辑器中作为条 件拆分后数据的归宿目标。把数据流从条件拆分组件连接 向数据目标时，会弹出“选择输入输出”对话框

17、。选择需 要的输出，数据流就创建好了。 .38 5、切换到控制流执行容器，在数据库中创建表。 编辑OLE DB目标，使其指向前面用SQL语句创建 的目标表。如下图所示。 注意：如果前面对Customers数据连接的设置中数 据类型没有设置正确描绘出现错误提示：列 “FirstName”无法在 unicode 和非 unicode 字符串 数据类型之间转换。 返回Customers数据连接，重 新编辑其字段的属性即可。 .39 .40 6、设置无效区域目标文件，指向 CustomersWithInvalidTerrritoryID平面文件连接。 .41 .42 7、在条件拆分中还有区域2的数据没

18、有目标宿主，这是因为 销售区域2在数据录入的时候邮编本应该是5位，而有些数 据由于省略了前面的0只输入了4位数字，因此还需要对邮 编进行清洗操作才能导入到表中。选择“派生列”组件， 按下图进行设置。在表达式中输入代码： LEN(PostalCode) = 4 ? 0 + PostalCode : PostalCode ，含义是如果 PostalCode字段的长度为4，则在前面加0，否则直接使用 PostalCode作为目标列的值。通过这种方法对数据进行了 有效的清洗。 .43 .44 8、将区域2的目标添加到数据流设计器中， 并与派生列组件连接起来。 .45 .46 五、包的部署 要使包能在不同的环境下完成ETL的功能，必须把包含包 的项目生成部署实用工具，然后安装包安装向导将包安装