SSIS学习之SSIS介绍

12.1 SSIS介绍

SSIS首先是在1997年的SQL Server 7.0中引入的，当时它的名称为数据转换服务(DTS)。SSIS属于ETL产品家族，ETL代表提取(Extraction)、转换(Transformation)和加载(Loading)。

现在，越来越多的企业都有数据仓库。ETL是将 来自OLTP数据库的数据定期加载到数据仓库中必不可少的工具。在SQL Server的前两个版本—— SQL Server 7.0和SQL Server 2000中，SSIS主要集中于提取和加载。通过使用SSIS，可以从任何数据源中提取数据以及将数据加载到任何数据源中。在SQL Server 2005中，对SSIS进行了重新设计和改进。SSIS提供控制流和数据流。控制流也称为工作流或者任务流，它更像工作流，在工作流中每个组件都是一个任 务。这些任务是按预定义的顺序执行的。在任务流中可能有分支。当前任务的执行结果决定沿哪条分支前进。

数据流是新的概念。数据流也称为流水线，主要解 决数据转换的问题。数据流由一组预定义的转换操作组成。数据流的起点通常是数据源(源表)；数据流的终点通常是数据的目的地(目标表)。可以将数据流的执 行认为是一个流水线的过程，在该过程中，每一行数据都是装配线中需要处理的零件，而每一个转换都是装配线中的处理单元。

图12-1显示了SSIS设计器。可以通过启动 Business Intelligence Development Studio，然后创建一个Integration Services项目来调用SSIS设计器。在左边有一个工具箱窗口。工具箱窗口包含预定义的控制流任务和数据流转换。中间的视图窗格包含4个视图：控制 流、数据流、事件处理程序和包资源管理器。控制流视图提供了一个设计环境，在这个设计环境中可以使用工具箱中与控制流相关的项来构建控制流。数据流视图也 提供了一个设计环境，在这个设计环境中可以使用工具箱中与数据流相关的项来构建数据流。在事件处理程序视图中，可以定义由特定的执行事件触发的后续响应操 作。包资源管理器视图提供了流的树型视图。

图12-1 SSIS设计器

12.1.1 理解SSIS包

包(package)是SSIS项目中基本的部 署和执行单元。SSIS任务执行的所有工作都发生在包的上下文中。SSIS包是SSIS流的容器。可以通过以下方式来创建SSIS包：在 Integration Services项目文件夹中右击“SSIS包”文件夹，然后选择“新建SSIS包”菜单项。

SSIS项目可以包含多个包。包只包含一个控制流，而该控制流可以包含一个或者多个数据流。

除了控制流和数据流，包还包含了SSIS连接和包的变量。可以有不同作用域的变量，例如，作用域为整个包的全局变量或者作用域为任务序列的局部变量。

在托盘中显示了连接和变量，如SSIS设计器底部所示(参见图12-1)。连接可以连向关系数据库，也可以连向Analysis Services数据库。

12.1.2 任务流

SSIS包包含一个控制流，该控制流由一组任务组成。这些任务定义了包的工作，例如，加载数据、执行SQL语句、处理脚本或者调用其他过程。在工具箱中列出了这些任务。可以通过以下方式将任务加入包中：从工具箱中拖动该任务，然后将它释放到包的设计器中。

在任务流中，包通常包含多个任务。多个任务是按优先权约束的顺序来进行组织的。优先权约束按以下顺序连接两个任务：执行第一个任务的结果决定是否运行第二个任务。可以在工作流中使用优先权约束来创建条件分支。可以将多个优先权约束进行组合，然后将其作为一个约束来求值。

1. 在SSIS中的标准任务

表12-1列出了SSIS任务流中的一些预构建的任务及其描述信息。

除了在下表中所列出的任务之外，SSIS还提供了简单的API，可以使用这些API来开发自己的任务。一旦注册了任务，则在工具箱中将会出现这些任务，然后可以在任务流中像所有预构建的任务一样使用这些任务。

表12-1 SSIS的任务

(续表)

2. 容器

容器(container)是向包提供结构的 SSIS对象。每个包都有一个容器，用来存储包的流。包的容器可以包含其他类型的容器，例如序列容器、Foreach循环容器和For循环容器，这些容器 都可以在控制流工具箱中获得。可以在这些容器中定义子流。容器丰富了控制流的运行时执行模型。例如，并不是所有的任务都是顺序执行的。在Foreach循 环容器内部的子流将会基于容器中定义的迭代器而多次执行。

3. 调试

对SSIS控制流进行调试是在SQL Server 2005中引入的新特性。当在设计器中选择了一个任务时，只要按下F9就可以设置断点。通过调试，可以在执行期间检查包的变量的值以及任务的状态。断点可 以基于事件来设置。例如，可以基于一个任务的以下事件来设置一个或者多个断点：

● OnPreExecute

● OnPostExecute

● OnError

● OnWarning

● OnProgress

当SSIS的运行时遇到了一个断点事件时，SSIS的运行时会在包的执行期间暂停。这与在Visual Studio中调试C#代码类似。

4. 研究一个控制流示例

图12-2显示了一个简单的控制流示例。它以 FTP任务开始，该FTP任务用于下载已压缩的数据文件。一旦下载完这些文件，运行时就会到达Foreach循环容器，在该容器内部有两个任务：执行进程 任务(该任务调用unzip应用程序来解压缩数据文件)和大容量插入任务(该任务将数据文件的数据插入到SQL Server中)。当解压缩完数据文件并且将数据文件的数据加载到SQL Server中时，循环会停止。如果在循环期间发生了错误，则该控制流将执行发送邮件任务：向DBA发送电子邮件。如果正确地执行了每个任务，则该控制流 将执行SQL任务：在新的数据集上创建一些视图。断点是在执行SQL任务的OnPreExecute事件上设置的，以便DBA在创建视图前可以对数据加以 验证。

图12-2 控制流的示例

12.1.3 数据流

控制流是出现较早的概念，因为它从SSIS的第一个版本开始就已经存在了，而数据流是在SQL Server 2005中才引入的新概念。数据流是专门处理数据操作的工作流。

数据流也称为流水线。可以将数据流认为是装配 线，该装配线包含了顺序执行的多个操作。在数据流中的每个节点都称为转换。数据流通常以源转换开始，以目标转换结束。在这两个转换之间，预定义的数据流转 换被依序应用到数据上。一些转换是同步的，例如，查找、条件性拆分和数据转换。这些同步的转换可以并行执行。

一旦已经将转换应用到数据行上，则下一个转换可以开始处理该数据行，而无需等到上一级转换处理完整个数据集。一些转换是异步的，例如聚合和排序。这些转换必须从前面的输出中获得所有的行，从而可以处理和产生输出，用于后续转换。

除了预构建的转换清单，SSIS还提供了一些API，可以使用这些API来构建自己的数据流转换。例如，可以构建Numeric Processing转换来将数学计算和转换应用到流水线数据上。

数据流总是包含于任务流中。有一个特殊的任务，其名称为数据流任务，该任务是用于容纳数据流的容器。在构建数据流之前，必须将数据流任务加入到设计器中。

1. 转换

任务是任务流的基本组件，而转换是数据流的基本组件。转换是预定义的数据操作。可以将转换视为装配线中的机器，用于对输入数据进行操作。表12-2显示了在SSIS数据流环境中流行的转换列表。

表12-2 SSIS的转换

2. 查看器

查看器是为了调试数据流而设计的。可以在执行期 间使用查看器来查看流水线中的流。可以将查看器附加在转换之间的连线上。要增加查看器，可以在连线上右击，然后选择“数据查看器”菜单项。默认的查看器是 网格图。也可以增加直方图，散点图和圆柱图作为图形查看器。如果这些预定义的查看器还不能满足您的需求，则您甚至可以构建自己的查看器。

3. 研究一个数据流示例

图12-3显示了一个数据流的示例。该数据流以 OLE DB Source转换开始，该转换加载包含零售销售事务的事实表。第二个转换是“查找”，该转换从客户维表中查找客户姓名中的名和姓。这两列被加入到流水线的 数据中。下一个转换是“派生列”，该转换基于姓名中的名和姓来创建新的Full Name列。然后，数据到达“聚合”转换，该转换累加每个客户的总销售额。基于销售额，“条件性拆分”转换将客户拆分为两个目标表：一个表对应重要的客 户，而另一个表对应普通的客户。

图12-3 数据流的示例

12.2 在SSIS环境中进行数据挖掘

SSIS提供了流的环境，在该环境中可以通过一组内置的任务和转换来进行数据提取、加载和转换。

正如您已经学习的，在数据挖掘项目中最消耗资源 的工作是数据清理和转换。SSIS自然可以作为数据挖掘项目很好的补充。可以使用这个强大的工具来从不同的源中加载数据，将这些数据连接在一起，规范化列 的值，移除无用的记录，替换缺失值，以及将数据分成训练数据集和测试数据集等。

SSIS不仅仅作为数据挖掘的ETL工具，SSIS实际上还在控制流和数据流环境中提供了一些内置的数据挖掘组件。特别地，在表12-3中列出了一些专门用于数据挖掘的任务和转换。

表12-3 用于数据挖掘的任务和转换

12.2.1 数据挖掘任务

在本节中，我们将更详细地介绍一些与数据挖掘相关的任务。

1. 数据挖掘查询任务

数据挖掘查询任务用于执行数据挖掘的查询，主要是执行SSIS控制流中的预测查询。

图12-4显示了对话框的“挖掘模型”选项卡，该对话框用于编辑数据挖掘查询任务的属性。在该选项卡中，首先将连接指定到活跃的Analysis Services数据库。然后必须指定查询所基于的挖掘结构和挖掘模型。

图12-5显示了数据挖掘查询任务的“查询”选 项卡。在该选项卡中，必须输入数据挖掘查询语句。可以直接在文本框中编写查询语句，也可以单击“生成新查询”按钮来调用预测查询生成器。预测查询生成器提 供了图形化的环境，在该环境中可以构建DMX查询。在第3章中已经学习了如何使用该工具。

图12-4 数据挖掘查询任务——选择挖掘模型

图12-5 数据挖掘查询任务——构建查询

DMX查询可以包含一些参数。这些参数可以映射到SSIS变量。例如，您可能希望编写一个查询来返回一些客户，这些客户拥有黄金会员卡的概率超过给定的值。该给定的值可以通过SSIS变量Probability来设置。

“结果集”选项卡提供了一个选项，该选项可用于将查询返回的结果映射到一个SSIS变量，比如一个集合变量。

图12-6显示了数据挖掘查询任务的“输出”选项卡。在这个选项卡中，可以指定连接的目标数据库，在该目标数据库中将保存查询结果。它可以是与源数据相同的数据库，也可以是不同的数据库。您还需要对结果表进行命名。

图12-6 数据挖掘查询任务——指定输出

注意：

DMX查询可以产生嵌套的结果，例如，如果在Select子句中使用PredictHistogram函数，则数据挖掘查询任务将自动平坦化嵌套行集，然后在一个表中插入查询结果。

2. Analysis Services处理任务

Analysis Services处理任务用于处理Analysis Services数据库中的主要对象。这些对象可以是维、立方体、挖掘结构和挖掘模型。

图12-7显示了该任务的编辑器。可以使用“添加”按钮来选择对象，也可以在“处理选项”列中指定处理的选项和设置。

图12-7 Analysis Services处理任务

在预测查询生成器中执行DMX查询

除了DMX预测查询，还可以在预测查询生成器中执行其他类型的DMX查询。例如，可以输入以下的内容查询：

Select * from myAssociationModel.Content

Where Node_Type = 7

也可以调用存储过程：

Call mystoreproc

实际上，也可以在数据挖掘查询任务中指定DMX的Creation语句和Insert Into语句。可以使用该任务来创建和处理挖掘模型。

3. Analysis Services执行DDL任务

DDL(数据定义语言)可以采用XML格式来描述。在Analysis Services 2005中，所有的对象(挖掘模型、挖掘列、维、立方体和角色等等)都是使用DDL来定义的(在第14章中将学习DDL的基本概念)。

除了对象的定义，DDL还包含了一组命令。这些命令使创建、更新、处理和删除对象变得容易。

当使用图形化的工具(例如挖掘模型编辑器)来创建挖掘模型时，这些工具会产生发送给服务器的DDL脚本。

如果不使用数据挖掘编辑器来创建/更新这些对象，则可以复制或者编写这些DDL脚本，然后通过执行DDL任务来执行它们。

图12-8显示了执行DDL任务的编辑器。首 先，必须指定与Analysis Services的连接。然后单击SourceDirect属性，该属性调用用来显示DDL命令的文本框。在图12-8中的DDL用于在 Adventure Works DW数据库中创建挖掘结构DM Customers。

图12-8 Analysis Services执行DDL任务

4. 一个使用数据挖掘的控制流示例

图12-9显示了使用数据挖掘查询任务的控制流 示例。控制流首先通过FTP任务下载新客户的数据集，然后使用大容量插入任务将数据加载到SQL Server数据库中。如果在数据加载期间发生了错误，则它会向DBA发送电子邮件。如果数据加载成功，则它会执行数据挖掘查询任务，该任务用于查找那些 对特殊的促销产品可能感兴趣的客户。预测的结果会保存在表中。在预测之后，它会给可能感兴趣的客户发送电子邮件。

图12-9 使用数据挖掘查询的控制流

12.2.2 数据挖掘转换

在本节中，我们将更详细地论述与数据挖掘相关的转换。

1. 数据挖掘模型定型转换

数据挖掘模型定型转换(简称DM定型转换)用于在数据流环境中处理挖掘结构及其相关的挖掘模型。

Analysis Services处理任务也可以处理挖掘模型，但是必须在任务中指定输入表。在执行期间，AS服务器从存储了训练数据的源表中提取数据。然而，在数据流环 境中，DM定型转换在执行期间将数据从流水线推到Analysis Services。AS服务器以推的模式获得训练数据。在将流水线数据发送给AS服务器之前，DM定型转换以XML行集的格式对该数据进行封装。

图12-10显示了DM定型转换的属性对话框的“连接”选项卡。在该选项卡中，可以指定到AS Server数据库的连接，然后选择您希望使用流水线数据来处理的挖掘结构。

也可以使用“新建”按钮来创建新的挖掘结构。单 击该按钮后会启动数据挖掘向导。在该向导中，对模型创建过程的处理方式与在数据挖掘编辑器环境中的处理方式相同。然而，还存在一些不同的地方。首先，列是 来自流水线，而不是来自关系表。某些特性(例如，自动检测内容类型和相关建议)不可用。这是因为这些特性需要对输入数据进行扫描和采样，而在流水线环境中 这些操作是不可行的。

图12-11显示了DM定型转换的“列”选项卡，在该选项卡上可以指定流水线的列和挖掘结构的列之间的映射。在默认情况下，该工具会基于列名自动进行映射。

图12-10 DM定型转换——“连接”选项卡

图12-11 DM定型转换——“列”选项卡

提示：

虽然DM定型转换支持模型创建，但是对模型进行编辑(比如改变模型的参数)却相当困难。一种解决方案是在相同的解决方案中拥有两个BI项目：SSIS项目和AS项目。在这种情况下，可以在AS项目中使用挖掘模型编辑器，从而获得对于挖掘模型的完整编辑功能。

2. 数据挖掘查询转换

顾名思义，数据挖掘查询转换(简称DM查询转换)是用于执行数据挖掘查询的转换。在第2章中，已学习了不同类型的DMX查询，包括预测、内容、模型创建和模型训练。在这些查询中，预测查询是最常见的。

要执行预测查询，需要训练过的挖掘模型和输入数 据集。在流水线环境中，输入数据集是从流水线推来的。DM查询转换将来自流水线的输入数据封装为XML行集，然后通过XML/A协议将它们发送给AS服务 器。AS服务器执行查询，然后将XML结果返回给该转换。然后，该转换解析该结果，再将解析后的结果推给流水线，以便下一个转换进行处理。当输入数据比较 大时，该过程会以块的方式来处理：每次将一组行发送给AS服务器来进行预测。

图12-12显示了该转换的“挖掘模型”选项卡，在该选项卡上将连接指定到活跃的AS服务器的数据库，还必须选择挖掘结构和它的模型之一。

图12-12 DM查询转换——“挖掘模型”选项卡

在该转换的“查询”选项卡上可以输入DMX查 询。可以手动编写查询，也可以单击“生成新查询”按钮来启动预测查询生成器。预测的输入表是流水线的输入，表示为@InputRowset。DMX查询结 果可以包含嵌套的结果，例如，在Select语句中使用PredictionHistogram函数。在这种情况下，将会自动平坦化嵌套行集。

注意：

在SQL Server 2005中，SSIS数据流本身不支持嵌套行集。这一点会给数据挖掘的训练和预测增加技术上的挑战，因为它可能需要嵌套的输入，然后产生嵌套的输出。为了 解决这个问题，DM查询转换在有嵌套行集时会自动平坦化结果。然而，对于嵌套的输入，问题更大。当模型或者查询需要嵌套的输入时，推荐使用DM定型任务和 DM查询任务。

3. 数据流示例

图12-13显示了用于模型训练的数据流示例。 它以OLE DB数据源转换开始，该转换选择FoodMart Customer表。下一个转换是派生列转换，该转换创建了新的列Age，该列是从Birthdate列中派生的。一旦将新的列加入到流水线中，就会将数 据发送给DM定型转换，该转换并行地处理挖掘结构及其相关的挖掘模型。

图12-13 用于模型训练的数据流示例

图12-14显示了使用DM查询转换的数据流。 流水线以OLE DB源转换开始，该转换加载NewCustomer表。下一个转换是派生列，该转换添加从Birthdate列中派生的Age列。第三个转换是DM查询， 该转换使用已训练的决策树模型来为每个新的客户预测最有可能拥有的会员卡。预测的结果会发送给条件性拆分转换，该转换基于会员卡的类型将数据分成3个流水 线分支。左边的分支只包含金卡会员，中间的分支只包含银卡会员，而右边的分支包含铜卡会员和普通会员。每个分支都会通向OLE DB目标转换，在该转换中分别存储了VIP客户、重要的客户以及普通的客户。

图12-14 包含预测的数据流示例

该示例也说明了数据挖掘技术可以作为高级的ETL过程来应用。它使得数据拆分不仅可以基于已有的属性，还可以基于预测的属性。

4. 字词提取转换

字词提取转换用于构建特定领域中的关键术语的术 语表。这通常是文本挖掘项目的第一步。在输入流水线缓冲器中的列之一包含了某种类型(比如ntext或者nvarchar)的文本数据。该转换的目的是分 析这一列，然后基于这一列的内容来构建关键术语的字典。转换的输出是一个只含一列的表。提取关键术语不是想象中那么容易；存在许多复杂的技术，例如，单词 词干处理和相关的语法分析。该转换提取名词和名词短语，比如data mining。

字词提 取转换的用户界面非常简单。在第一个选项卡中，必须指定文本列。也可以对关键术语的输出列及其相关的分数进行命名，如图12-15所示。分数基于 TFIDF，TFIDF代表词频(Term Frequency)和倒文档词频(Inverse Document Frequency)。TFIDF是统计学中的一种技术，用于评估单词对文档的重要性。该重要性根据单词在文档中出现的次数而成比例地增加，但是会受单词 在收集的所有文档中出现的频率影响而发生偏移。

图12-15 字词提取转换——“字词提取”选项卡

移除数据流中的孤立点

可 以应用数据挖掘技术来移除数据流中的孤立点(outlier)。正如在第7章中所解释的，Microsoft聚类算法提供了预测函数 PredictCaseLikelihood，该函数返回某个事例适合于某个给定模型的似然估计。似然估计分数非常低的事例属于异常。可以使用DM查询转 换来执行针对流水线数据的预测查询，然后基于查询的结果来过滤异常。以下查询基于似然估计分数来识别来自数据集的前50个孤立点。

SELECT Top 50 t.CustomerKey,

PredictCaseLikelihood()

From

MyClusterModel

PREDICTION JOIN

@InputRowset AS t

ON

...

Order by PredictCaseLikelyhood() Desc

第二个选项卡(参见图12-16)给出了一个选项，该选项用于指定包含的术语和不包含的术语。您可能已经拥有必须包括的预定义的术语清单，以及不希望提取的术语清单。在这个选项卡中可以指定这两个术语清单。

图12-16 字词提取转换——“排除”选项卡

字词提取转换也提供用于术语的选项；例如，可以指明术语必须是单个词或者名词短语。对于名词短语，也可以提及最大长度。

5. 字词查找转换

字词查找转换用于(基于字典)从输入文本列中搜索关键术语。字典通常通过字词提取转换产生。因为字典就是表，所以当有必要时可以编写SQL查询来修改清单(通过增加或者删除术语)。

字词查找转换的编辑器相当简单。第一个选项卡用于指定引用表(字典)，如图12-17所示。

图12-17 字词查找转换——“引用表”选项卡

第二个选项卡用于指定列的映射，例如，将输入文本列映射到字典(参见图12-18)。也可以直接指定一些输入列，例如，文档ID。

字词查找转换产生两个新列作为输出：字词和词频。可以将该转换的输出认为是包含大量行的事实表(包含文档ID、关键术语和相关的词频)。

提示：

在许多情况下，字词查找转换产生的术语比需要用于文本挖掘的术语要多。也可能存在一些无用的术语。在将数据输入到挖掘模型之前，强烈推荐对查找转换的输出表进行浏览，然后移除那些认为无用的术语。

图12-18 字词查找转换——“字词查找”选项卡

6. 文本挖掘项目示例

在我们周围，非结构化的数据比结构化的数据要多 得多，比如Internet，它包含数十亿个非结构化的超文本标记语言(Hypertext Markup Language，HTML)文档。分析文本文档变得越来越重要。两个流行的用于文本挖掘的数据挖掘任务是分类和细分。流行的文本挖掘领域之一是客户支持 领域。在分类的示例中，有许多来自Web和电子邮件的客户反馈，我们希望为每个反馈指派优先权。在细分的示例中，我们希望将相似的反馈分组到一起，然后将 它们转发给正确的部门。

通过SQL Server 2005，可以使用SSIS和数据挖掘来实现文本挖掘项目。在一个典型的流中，第一步是使用字词提取来为商业领域创建字典。第二步是使用字词查找来从文本 列中搜索关键术语，从而将非结构化的数据转换为结构化的输出。第三步是应用数据挖掘技术(例如贝叶斯、神经网络、聚类和决策树)在字词查找的输出上构建模 型。通常，字词查找的输出用作挖掘模型中的嵌套表。

图12-19显示了使用关联算法的文本挖掘模型的示例。它分析了软件开发项目的bug描述数据库，并且确认了关键术语之间的关系。

图12-19 基于软件bug数据库的Term Association模型

12.3 本章小结

在本章中，学习了与另一个重要的SQL Server组件SQL Server集成服务(SSIS)相关的基本概念。我们介绍了控制流和数据流。还讨论了一些重要的任务和转换。SSIS是用于数据清理和转换(该步骤对于任何数据挖掘项目来说都是很耗时的)的重要工具。

本章中间部分的内容集中介绍了SSIS环境中特定于数据挖掘的特性。我们论述了每个与数据挖掘相关的任务和转换。最后，我们给出了基于SQL Server 2005的两个与文本挖掘相关的转换和一个典型的文本挖掘项目示例。

数据挖掘和SSIS能够从彼此中获得好处。SSIS为数据挖掘提供数据处理的环境，而数据挖掘技术可以用作数据转换过程的一部分。它使得SSIS更聪明，从而使得SSIS领先于其他经典ETL产品。

到现在为止，您应该清楚地理解了SSIS与数据挖掘之间的关系，以及可以在SSIS环境中完成的数据挖掘项目的类型。