SQLServer的SSIS的核心功能介绍

SQLServer的SSIS的核心功能介绍最近在用SQLSERVER的ssis工具导入另外一个系统的数据，一直没有静下来系统的总结一些其中的学习及研究收获，恰好找到一篇感觉介绍不错的文章，发扬一下“拿来主义”，收录在自己的地盘上。闲言少叙，一下是正文。1SSIS介绍SSIS首先是在1997年的SQLServer7.0中引入的，当时它的名称为数据转换服务(DTS)。SSIS属于ETL产品家族，ETL代表提取(Extraction)、转换(Transformation)和加载(Loading)。现在，越来越多的企业都有数据仓库。ETL是将来自OLTP数据库的数据定期加载到数据仓库中必不可少的工具。在SQLServer的前两个版本SQLServer7.0和SQLServer2000中，SSIS主要集中于提取和加载。通过使用SSIS，可以从任何数据源中提取数据以及将数据加载到任何数据源中。在SQLServer2005中，对SSIS进行了重新设计和改进。SSIS提供控制流和数据流。控制流也称为工作流或者任务流，它更像工作流，在工作流中每个组件都是一个任务。这些任务是按预定义的顺序执行的。在任务流中可能有分支。当前任务的执行结果决定沿哪条分支前进。数据流是新的概念。数据流也称为流水线，主要解决数据转换的问题。数据流由一组预定义的转换操作组成。数据流的起点通常是数据源(源表)；数据流的终点通常是数据的目的地(目标表)。可以将数据流的执行认为是一个流水线的过程，在该过程中，每一行数据都是装配线中需要处理的零件，而每一个转换都是装配线中的处理单元。下图显示了SSIS设计器。可以通过启动BusinessIntelligeneeDevelopmentStudio,然后创建一个IntegrationServices项目来调用SSIS设计器。在左边有一个工具箱窗口。工具箱窗口包含预定义的控制流任务和数据流转换。中间的视图窗格包含4个视图：控制流、数据流、事件处理程序和包资源管理器。控制流视图提供了一个设计环境，在这个设计环境中可以使用工具箱中与控制流相关的项来构建控制流。数据流视图也提供了一个设计环境，在这个设计环境中可以使用工具箱中与数据流相关的项来构建数据流。在事件处理程序视图中，可以定义由特定的执行事件触发的后续响应操作。包资源管理器视图提供了流的树型视图。芷獰*Mifl■甜即關尅密£JE>li啲密 矗養⑴ 芷獰*Mifl■甜即關尅密£JE>li啲密 矗養⑴ '工JI形g曲匹即昭脚出1需匹鼻#?ifimwwks*_SPLlfSLM上，执刊営軋鼻七需聃豐祸世券.珀删疳富母锂鲁二WWITTSWlflWau-tqH-ik'JLE!-fLi-:kx-MklT3!i.liF^r2理解SSIS包包(package)是SSIS项目中基本的部署和执行单元。SSIS任务执行的所有工作都发生在包的上下文中。SSIS包是SSIS流的容器。可以通过以下方式来创建SSIS包：在IntegrationServices项目文件夹中右击“SSIS包〃文件夹，然后选择''新建SSIS包〃菜单项。SSIS项目可以包含多个包。包只包含一个控制流，而该控制流可以包含一个或者多个数据流。除了控制流和数据流，包还包含了SSIS连接和包的变量。可以有不同作用域的变量，例如，作用域为整个包的全局变量或者作用域为任务序列的局部变量。在托盘中显示了连接和变量，如SSIS设计器底部所示(参见图12-1)。连接可以连向关系数据库，也可以连向AnalysisServices数据库。3任务流SSIS包包含一个控制流，该控制流由一组任务组成。这些任务定义了包的工作，例如，加载数据、执行SQL语句、处理脚本或者调用其他过程。在工具箱中列出了这些任务。可以通过以下方式将任务加入包中：从工具箱中拖动该任务，然后将它释放到包的设计器中。在任务流中，包通常包含多个任务。多个任务是按优先权约束的顺序来进行组织的。优先权约束按以下顺序连接两个任务：执行第一个任务的结果决定是否运行第二个任务。可以在工作流中使用优先权约束来创建条件分支。可以将多个优先权约束进行组合，然后将其作为一个约束来求值。3.1在SSIS中的标准任务先面列出了SSIS任务流中的一些预构建的任务及其描述信息。除了在以下所列出的任务之外，SSIS还提供了简单的API,可以使用这些API来开发自己的任务。一旦注册了任务，则在工具箱中将会出现这些任务，然后可以在任务流中像所有预构建的任务一样使用这些任务。大容量插入任务——将来自文本文件的大量数据加载到SQLServer表中数据流任务——支持在不同类型的数据源之间进行数据的复制和转换。一个数据流任务包含一条数据流流水线执行包任务——运行子包执行进程任务——将程序或者批处理文件作为包的一部分来运行执行SQL任务一一在包的执行期间运行SQL语句，然后有选择地保存这些查询的结果文件系统任务——执行文件系统操作FTP任务一一从远程服务器或者Internet的某个位置下载数据文件，并且将这些数据文件作为包的工作流的一部分消息队列任务——在SSIS包之间使用消息队列来发送和接收消息脚本任务使用脚本来执行在预构建的SSIS任务中不可用的函数。通过使用Script任务，可以在MicrosoftVisualStudioforApplications(VSA)环境中使用VisualBasic.NET来编写脚本发送邮件任务——发送电子邮件消息XML任务一一合并、过滤和转换XML文档中的数据容器容器(container)是向包提供结构的SSIS对象。每个包都有一个容器，用来存储包的流。包的容器可以包含其他类型的容器，例如序列容器、Foreach循环容器和For循环容器，这些容器都可以在控制流工具箱中获得。可以在这些容器中定义子流。容器丰富了控制流的运行时执行模型。例如，并不是所有的任务都是顺序执行的。在Foreach循环容器内部的子流将会基于容器中定义的迭代器而多次执行。调试对SSIS控制流进行调试是在SQLServer2005中引入的新特性。当在设计器中选择了一个任务时，只要按下F9就可以设置断点。通过调试，可以在执行期间检查包的变量的值以及任务的状态。断点可以基于事件来设置。例如，可以基于一个任务的以下事件来设置一个或者多个断点：OnPreExecuteOnPostExecute•OnError•OnWarning•OnProgress当SSIS的运行时遇到了一个断点事件时，SSIS的运行时会在包的执行期间暂停。这与在VisualStudio中调试C#代码类似。3.4研究一个控制流示例下图显示了一个简单的控制流示例。它以FTP任务开始，该FTP任务用于下载已压缩的数据文件。一旦下载完这些文件，运行时就会到达Foreach循环容器，在该容器内部有两个任务：执行进程任务(该任务调用unzip应用程序来解压缩数据文件)和大容量插入任务(该任务将数据文件的数据插入到SQLServer中)。当解压缩完数据文件并且将数据文件的数据加载到SQLServer中时，循环会停止。如果在循环期间发生了错误，则该控制流将执行发送邮件任务：向DBA发送电子邮件。如果正确地执行了每个任务，则该控制流将执行SQL任务：在新的数据集上创建一些视图。断点是在执行SQL任务的OnPreExecute事件上设置的，以便DBA在创建视图前可以对数据加以验证。4.数据流控制流是出现较早的概念，因为它从SSIS的第一个版本开始就已经存在了，而数据流是在SQLServer2005中才引入的新概念。数据流是专门处理数据操作的工作流。数据流也称为流水线。可以将数据流认为是装配线，该装配线包含了顺序执行的多个操作。在数据流中的每个节点都称为转换。数据流通常以源转换开始，以目标转换结束。在这两个转换之间，预定义的数据流转换被依序应用到数据上。一些转换是同步的，例如，查找、条件性拆分和数据转换。这些同步的转换可以并行执行。一旦已经将转换应用到数据行上，则下一个转换可以开始处理该数据行，而无需等到上一级转换处理完整个数据集。一些转换是异步的，例如聚合和排序。这些转换必须从前面的输出中获得所有的行，从而可以处理和产生输出，用于后续转换。除了预构建的转换清单，SSIS还提供了一些API，可以使用这些API来构建自己的数据流转换。例如，可以构建NumericProcessing转换来将数学计算和转换应用到流水线数据上。数据流总是包含于任务流中。有一个特殊的任务，其名称为数据流任务，该任务是用于容纳数据流的容器。在构建数据流之前，必须将数据流任务加入到设计器中。4.1转换任务是任务流的基本组件，而转换是数据流的基本组件。转换是预定义的数据操作。可以将转换视为装配线中的机器，用于对输入数据进行操作。表12-2显示了在SSIS数据流环境中流行的转换列表。SSIS的转换''聚合”转换 执行聚集，例如average、sum和count“字符映射表”转换——将字符串函数应用到字符数据上'条件性拆分”转换——基于指定的标准来将数据行路由到不同的输出'复制列”转换——将输入列的副本添加到转换的输出中'数据转换”转换——将列的数据类型转换为不同的数据类型'派生列”转换——使用表达式来生成从现有列中派生的新列''处理维度〃转换 处理AnalysisServices的维'模糊分组”转换——通过以下方式来执行数据清理的任务：首先识别可能重复的数据行，然后选择规范的数据行来用于对数据进行标准化'模糊查找”转换——在引用表中使用模糊匹配来查找值'查找”转换——在引用表中使用精确匹配来查找值'合并”转换——合并两个已分类的数据集''合并联接〃转换一一使用FULL、LEFT或者INNERjoin来连接两个已分类的数据集'多播”转换——将数据集分布到多个输出中'分区处理”转换——处理AnalysisServices的划分'透视”转换——创建规范表的较低规范化版本'排序”转换——对流水线的数据进行分类“UnionAll”转换一一创建多个数据集的并集'逆透视”转换——创建不规范表的较规范版本4.2查看器查看器是为了调试数据流而设计的。可以在执行期间使用查看器来查看流水线中的流。可以将查看器附加在转换之间的连线上。要增加查看器，可以在连线上右击，然后选择'数据查看器”菜单项。默认的查看器是网格图。也可以增加直方图，散点图和圆柱图作为图形查看器。如果这些预定义的查看器还不能满足您的需求，则您甚至可以构建自己的查看器。4.3研究一个数据流示例图12-3显示了一个数据流的示例。该数据流以OLEDBSource转换开始，该转换加载包含零售销售事务的事实表。第二个转换是'查找”，该转换从客户维表中查找客户姓名中的名和姓。这两列被加入到流水线的数据中。下一个转换是'派生列”，该转换基于姓名中的名和姓来创建新的FullName列。然后，数据到达''聚合〃转换，该转换累加每个客户的总销售额。基于销售额，'条件性拆分”转换将客户拆分为两个目标表：一个表对应重要的客户，而另一个表对应普通的客户。5.在SSIS环境中进行数据挖掘SSIS提供了流的环境，在该环境中可以通过一组内置的任务和转换来进行数据提取、加载和转换。正如您已经学习的，在数据挖掘项目中最消耗资源的工作是数据清理和转换°SSIS自然可以作为数据挖掘项目很好的补充。可以使用这个强大的工具来从不同的源中加载数据，将这些数据连接在一起，规范化列的值，移除无用的记录，替换缺失值，以及将数据分成训练数据集和测试数据集等。SSIS不仅仅作为数据挖掘的ETL工具，SSIS实际上还在控制流和数据流环境中提供了一些内置的数据挖掘组件。特别地，在下表中列出了一些专门用于数据挖掘的任务和转换。用于数据挖掘的任务和转换数据挖掘查询任务——基于数据挖掘模型来运行预测查询AnalysisServices处理任务——处理需要分析的对象，例如立方体、维、划分和数据挖掘模型AnalysisServices执行DDL任务 运行数据定义语言(DataDefinitionLanguage,DDL)代码，这些代码可以创建、修改、删除和处理需要分析的对象数据挖掘查询转换——执行数据挖掘预测查询数据挖掘模型定型转换——使用流水线数据作为输入来处理数据挖掘模型字词提取转换——处理文本列来提取关键术语(单个词或者较短的名词短语)。提取的术语可以用来作为字词查找转换的字典字词查找转换——从输入的文本列中基于字典来搜索和提取关键术语。字词查找的结果可以用作文本挖掘的训练数据数据挖掘任务数据挖掘查询任务数据挖掘查询任务用于执行数据挖掘的查询，主要是执行SSIS控制流中的预测查询。

下图显示了对话框的“挖掘模型”选项卡，该对话框用于编辑数据挖掘查询任务的属性。在该选项卡中，首先将连接指定到活跃的AnalysisServices数据库。然后必须指定查询所基于的挖掘结构和挖掘模型。下图显示了数据挖掘查询任务的“查询”选项卡。在该选项卡中，必须输入数据挖掘查询语句。可以直接在文本框中编写查询语句，也可以单击“生成新查询”按钮来调用预测查询生成器。预测查询生成器提供了图形化的环境，在该环境中可以构建DMX查询。在第3章中已经学习了如何使用该工具。DMX查询可以包含一些参数。这些参数可以映射到SSIS变量。例如，您可能希望编写一个查询来返回一些客户，这些客户拥有黄金会员卡的概率超过给定的值。该给定的值可以通过SSIS变量Probability来设置。

''结果集〃选项卡提供了一个选项，该选项可用于将查询返回的结果映射到一个SSIS变量，比如一个集合变量。下图显示了数据挖掘查询任务的'输出”选项卡。在这个选项卡中，可以指定连接的目标数据库，在该目标数据库中将保存查询结果。它可以是与源数据相同的数据库，也可以是不同的数据库。您还需要对结果表进行命名。DMX查询可以产生嵌套的结果，例如，如果在Select子句中使用PredictHistogram函数，则数据挖掘查询任务将自动平坦化嵌套行集，然后在一个表中插入查询结果。5.1.2AnalysisServices处理任务AnalysisServices处理任务用于处理AnalysisServices数据库中的主要对象。这些对象可以是维、立方体、挖掘结构和挖掘模型。下图显示了该任务的编辑器。可以使用“添加”按钮来选择对象，也可以在“处理选项”列中指定处理的选项和设置。在预测查询生成器中执行DMX查询除了DMX预测查询，还可以在预测查询生成器中执行其他类型的DMX查询。例如，可以输入以下的内容查询：viewsourceprint?1Select*frommyAssociationModel.ContentWhereNode_Type=7也可以调用存储过程：viewsourceprint?1Callmystoreproc实际上，也可以在数据挖掘查询任务中指定DMX的Creation语句和InsertInto语句。可以使用该任务来创建和处理挖掘模型。5.1.3AnalysisServices执行DDL任务DDL（数据定义语言）可以采用XML格式来描述。在AnalysisServices2005中，所有的对象（挖掘模型、挖掘列、维、立方体和角色等等）都是使用DDL来定义的（在第14章中将学习DDL的基本概念）。除了对象的定义，DDL还包含了一组命令。这些命令使创建、更新、处理和删除对象变得容易。当使用图形化的工具（例如挖掘模型编辑器）来创建挖掘模型时，这些工具会产生发送给服务器的DDL脚本。如果不使用数据挖掘编辑器来创建/更新这些对象，则可以复制或者编写这些DDL脚本，然后通过执行DDL任务来执行它们。

下图显示了执行DDL任务的编辑器。首先，必须指定与AnalysisServices的连接。然后单击SourceDirect属性，该属性调用用来显示DDL命令的文本框。在图12-8中的DDL用于在AdventureWorksDW数据库中创建挖掘结构DMCustomers。r 创抒丽展用时bdl歯锢nu」井配■用于r 创抒丽展用时bdl歯锢nu」井配■用于it璋ue诵锅trw乩“5□OILSbut-c^Th1*SwciDiriiic^IffhifcrliXt.IkdiTil-ttV*t+F^SffI4凶^CfSSttTinWMWW^OOQ1-01-fllTftJOGOO.QOQQQQO-ffSV)<cwa?Tinfw«aJrtp><*38clwmaUpd™^MM1-D1-flJTW)W00(BOOOOO-0600打LasfSchc*riaupciae>^LwjPfM^od»0Wl-OiOHM00MOOWttfrOS如护UnpWE椚询网5爾护mngSjrwIinei*顶e询AKlWvG&MCU5TWW5</0>Cusfwner5^Meme><r««MiTima54emp=-00Cil-(ii4>lTOO»«WttXWWWk/C<«Mwrrima5ta<np><a^ch&™upd3te>0001.01-01T0O00000000000-(^00<；L曲l^m^Updsl日■«AnnotabOTS><AnrK4afiiE<hba™>hnpA'schemasnnicfosoftciDmCBflaWarehauM?D&signer/I0CawTaWe+J日*Va^^dba_L>h1Cu5£(me<s<Wafee>^Arih6iati6rt>*1lEft»L5.1.4一个使用数据挖掘的控制流示例下图显示了使用数据挖掘查询任务的控制流示例。控制流首先通过FTP任务下载新客户的数据集，然后使用大容量插入任务将数据加载到SQLServer数据库中。如果在数据加载期间发生了错误，则它会向DBA发送电子邮件。如果数据加载成功，则它会执行数据挖掘查询任务，该任务用于查找那些对特殊的促销产品可能感兴趣的客户。预测的结果会保存在表中。在预测之后，它会给可能感兴趣的客户发送电子邮件。5.2数据挖掘转换5.2.1数据挖掘模型定型转换数据挖掘模型定型转换（简称DM定型转换）用于在数据流环境中处理挖掘结构及其相关的挖掘模型。AnalysisServices处理任务也可以处理挖掘模型，但是必须在任务中指定输入表。在执行期间，AS服务器从存储了训练数据的源表中提取数据。然而，在数据流环境中，DM定型转换在执行期间将数据从流水线推到AnalysisServices。AS服务器以推的模式获得训练数据。在将流水线数据发送给AS服务器之前，DM定型转换以XML行集的格式对该数据进行封装。下图显示了DM定型转换的属性对话框的''连接〃选项卡。在该选项卡中，可以指定到ASServer数据库的连接，然后选择您希望使用流水线数据来处理的挖掘结构。也可以使用'新建”按钮来创建新的挖掘结构。单击该按钮后会启动数据挖掘向导。在该向导中，对模型创建过程的处理方式与在数据挖掘编辑器环境中的处理方式相同。然而，还存在一些不同的地方。首先，列是来自流水线，而不是来自关系表。某些特性（例如，自动检测内容类型和相关建议）不可用。这是因为这些特性需要对输入数据进行扫描和采样，而在流水线环境中这些操作是不可行的。

下图显示了DM定型转换的''列〃选项卡，在该选项卡上可以指定流水线的列和挖掘结构的列之间的映射。在默认情况下，该工具会基于列名自动进行映射。提示：虽然DM定型转换支持模型创建，但是对模型进行编辑（比如改变模型的参数）却相当困难。一种解决方案是在相同的解决方案中拥有两个BI项目：SSIS项目和AS项目。在这种情况下，可以在AS项目中使用挖掘模型编辑器，从而获得对于挖掘模型的完整编辑功能。5.2.2数据挖掘查询转换顾名思义，数据挖掘查询转换（简称DM查询转换）是用于执行数据挖掘查询的转换。在第2章中，已学习了不同类型的DMX查询，包括预测、内容、模型创建和模型训练。在这些查询中，预测查询是最常见的。要执行预测查询，需要训练过的挖掘模型和输入数据集。在流水线环境中，输入数据集是从流水线推来的。DM查询转换将来自流水线的输入数据封装为XML行集，然后通过XML/A协议将它们发送给AS服务器。AS服务器执行查询，然后将XML结果返回给该转换。然后，该转换解析该结果，再将解析后的结果推给流水线，以便下一个转换进行处理。当输入数据比较大时，该过程会以块的方式来处理：每次将一组行发送给AS服务器来进行预测。

下图显示了该转换的''挖掘模型〃选项卡，在该选项卡上将连接指定到活跃的AS服务器的数据库，还必须选择挖掘结构和它的模型之一。在该转换的''查询”选项卡上可以输入DMX查询。可以手动编写查询，也可以单击''生成新查询”按钮来启动预测查询生成器。预测的输入表是流水线的输入,表示为@1nputRowset。DMX查询结果可以包含嵌套的结果，例如，在Select语句中使用PredictionHistogram函数。在这种情况下，将会自动平坦化嵌套行集。在SQLServer2005中，SSIS数据流本身不支持嵌套行集。这一点会给数据挖掘的训练和预测增加技术上的挑战，因为它可能需要嵌套的输入，然后产生嵌套的输出。为了解决这个问题，DM查询转换在有嵌套行集时会自动平坦化结果。然而，对于嵌套的输入，问题更大。当模型或者查询需要嵌套的输入时，推荐使用DM定型任务和DM查询任务。5.2.3数据流示例下图显示了用于模型训练的数据流示例。它以OLEDB数据源转换开始，该转换选择FoodMartCustomer表。下一个转换是派生列转换，该转换创建了新的列Age，该列是从Birthdate列中派生的。一旦将新的列加入到流水线中，就会将数据发送给DM定型转换，

该转换并行地处理挖掘结构及其相关的挖掘模型。下图显示了使用DM查询转换的数据流。流水线以OLEDB源转换开始，该转换加载NewCustomer表。下一个转换是派生列，该转换添加从Birthdate列中派生的Age列。第三个转换是DM查询，该转换使用已训练的决策树模型来为每个新的客户预测最有可能拥有的会员卡。预测的结果会发送给条件性拆分转换，该转换基于会员卡的类型将数据分成3个流水线分支。左边的分支只包含金卡会员，中间的分支只包含银卡会员，而右边的分支包含铜卡会员和普通会员。每个分支都会通向OLEDB目标转换，在该转换中分别存储了VIP客户、重要的客户以及普通的客户。该示例也说明了数据挖掘技术可以作为高级的ETL过程来应用。它使得数据拆分不仅可以基于已有的属性，还可以基于预测的属性。5.2.4字词提取转换字词提取转换用于构建特定领域中的关键术语的术语表。这通常是文本挖掘项目的第一步在输入流水线缓冲器中的列之一包含了某种类型(比如ntext或者nvarchar)的文本数据。该转换的目的是分析这一列，然后基于这一列的内容来构建关键术语的字典。转换的输出是一个只含一列的表。提取关键术语不是想象中那么容易；存在许多复杂的技术，例如，单词词干处理和相关的语法分析。该转换提取名词和名词短语，比如datamining。字词提取转换的用户界面非常简单。在第一个选项卡中，必须指定文本列。也可以对关键术语的输出列及其相关的分数进行命名，如下图所示。分数基于TFIDF,TFIDF代表词频(TermFrequency)和倒文档词频(InverseDocumentFrequency)。TFIDF是统计学中的一种技术，用于评估单词对文档的重要性。该重要性根据单词在文档中出现的次数而成比例地增加，但是会受单词在收集的所有文档中出现的频率影响而发生偏移。5.2.5移除数据流中的孤立点可以应用数据挖掘技术来移除数据流中的孤立点(outlier)。正如在第7章中所解释的，Microsoft聚类算法提供了预测函数PredictCaseLikelihood,该函数返回某个事例适合于某个给定模型的似然估计。似然估计分数非常低的事例属于异常。可以使用DM查询转换来执行针对流水线数据的预测查询，然后基于查询的结果来过滤异常。以下查询基于似然估计分数来识别来自数据集的前50个孤立点。SELECTTop50t.CustomerKey,PredictCaseLikelihood()FromMyClusterModelPREDICTIONJOIN@InputRowsetAStON

OrderbyPredictCaseLikelyhood（）Desc第二个选项卡（参见下图）给出了一个选项，该选项用于指定包含的术语和不包含的术语。您可能已经拥有必须包括的预定义的术语清单，以及不希望提取的术语清单。在这个选项卡中可以指定这两个术语清单。字词提取转换也提供用于术语的选项；例如，可以指明术语必须是单个词或者名词短语。对于名词短语，也可以提及最大长度。5.2.5字词查找转换字词查找转换用于（基于字典）从输入文本列中搜索关键术语。字典通常通过字词提取转换产生。因为字典就是表，所以当有必要时可以编写SQL查询来修改清单（通过增加或者删除术语）。

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