SQL Server 2008集成服务详解

将公共数据转化为有意义的、可用的信息是现今商业世界中最重要的竞争优势来源了。将数据显示得更易于理解以及找到未来的方向已经变成全球企业的企业信息技术部门最大的挑战之一了。有三个与数据集成相关的大分类： • 技术挑战 • 公司问题 • 经济挑战 在这篇文章里，我们将详细地探讨这些挑战，并讨论怎样使用Microsoft SQL Server 2008集成服务(SSIS)来解决它们。首先，你应该在真实环境中查看下这些问题。 1.1 一个真实场景 一个大型的全球运输公司使用它的数据仓库来分析它操作的性能以及来预测它预定运输的可能变化。 1.2 数据源 公司的主要数据来源包括从它的基于DB2订单登陆系统而来得的订单数据、从它基于SQL Server的客户关系管理系统(CRM)而来的客户数据、以及从它基于Oracle的ERP系统而来的零售数据。除了从这些主要系统而来的数据之外，你可以将跟踪“特别”事件的电子数据表数据合并到数据仓库里，这些数据是由船运管理员手动输入的。目前，你可以合并延时地从多种来源的文本文件获得的外部数据，例如天气信息、交通状况、和销售细节(对于转包合同的运输)。 1.3 数据消耗 不只是这些数据来源不同，消费者在他们的需求和地理位置方面也是不同的。这个多样性导致了本地系统的扩张。信息技术部门的一个主要作用是至少为它的客户数据建立一个“真实的单一版本”。 1.4 数据集成要求 由于数据、商业需求和用户要求的多样性，信息技术部门制定了下面的数据集成要求： • 它们必须提供从内部和外部数据源集成获得的可靠而一致的历史和最新数据。 • 为了降低数据获取方面的延迟，从供应商和零售商处获得的数据必须可以通过Web services 或一些其它的直接机制——例如FTP——来获得。 • 它们需要清理和删除重复数据，否则就加强数据质量。 • 逐渐增加的全球调整性 2、 数据集成的挑战 某一层面上，在我们实际场景中数据集成的问题是非常简单的。从多个数据源获取数据、清理和转化数据、并将这些数据加载进适当的数据存储以用于分析和生成报表。不幸的是，在一个典型的数据仓库或商业智能项目中，企业在数据集成方面花费了60–80%的可用资源。为什么它这么困难呢? 2.1 技术挑战 技术挑战始于源系统。我们正从在事务上收集数据(用户提交以获得、购买或获取某些东西)转移到在事务之前收集数据(使用像Web点击或RFID标签之类的机制跟踪客户意愿)。现在数据不只是通过传统的源和格式获得——例如数据库和文本文件，还逐渐地可从多种不同的格式(从私有文件到Microsoft Office文档到基于XML文档)以及从基于网络来源——例如Web services和RSS流中获得。最相关的挑战是： • 不同格式的多种来源。 • 结构化的，半结构化的，以及非结构化的数据。 • 从不同来源系统而来的数据到达的时间不同。 • 数据量巨大。 在理想情况下，不管你怎样管理以获得一次所需的所有数据，都会要面临新的挑战，包括： • 数据质量。 • 使用不同的数据格式。 • 将数据转换为业务分析可用的格式。 假设你可以神奇地获得你需要的所有数据，并且你可以将这些数据清理、转化并匹配成一个有用的格式。还有另一种从传统数据移动和集成的方法。那就是从固定长度的面向批量处理转到可变长度和更短的按需处理。大多数公司在用户没有对系统有大需求的“当机”期间执行面向批量处理。这通常是在晚上认为没有人在办公室的时候，执行一个预定义的批量窗口，大概要6-8小时。随着全球商业的规模和类型的不断增加，这不再成为可能了。现在全球有些地方几乎没有当机时间，而且办公室里总是有人。 所以你需要： • 要尽快将数据加载的要求越来越高。 • 需要同时加载多目的地。 • 多样的目的地。 你不只需要实现所有这些结果，你还需要尽快地完成。在某些极端情况下，例如在线业务，你必须连续地集成数据。它没有真正的批量窗口，而且延迟时间不能超过几分钟。在许多这样的情况下，制定决策过程就会自动地使用连续运行的软件。 可扩展性和性能变得越来越重要，因为你面临着不能容忍任何当机的商业需求。 没有正确的技术，系统要求几乎存储进仓库和集成过程中的每一步都要分级。因为不同的数据源需要包含进提取、转换和加载(ETL)过程中，而且更复杂的操作(例如数据和文本挖掘)需要执行于数据，所以数据分级的要求越来越高了。正如图1中所显示的，随着分级的增加，“结束循环”(例如分析、对新数据执行操作)所耗费的时间也随之增加了。这些传统的ELT架构(相对于在加载之前进行的增值ETL处理)极大限制了系统响应不断出现的商业需求。 图1 图1 最后，当你需要应用程序集成的实时事务型技术以及面向批处理的大型数据集成技术来解决企业的商业问题时，关于数据集成是怎样紧密地绑定到公司的整个集成架构中去的问题就更为重要了。 2.2 公司挑战 在大型公司里，数据集成有两个大问题，就是“力量”挑战以及“舒适区域”挑战。 2.2.1 力量挑战 数据就是力量，并且通常很难使人们将数据看作公司的真实有价共享资产。要成功地进行企业数据集成，那么所有的多数据源所有者必须了解这个项目的目的和发展方向。缺乏相关部分的协作是数据集成项目失败的一个主要原因。行政赞助、构建共识和具有几个资金管理人的强大数据集成团队是可以帮助解决问题的一些成功关键因素。

2.2.2 舒适区域挑战

当你以多种方式分析一个单独需求的上下文时，你可以解决数据集成方面的挑战。手写代码实现大约60%的数据集成。解决类似问题所使用的技术从复制、ETL、SQL，到企业应用程序集成(EAI)。人们倾向于使用他们所熟悉的技术。尽管这些方法具有重叠的功能并可能在单独的情况下进行这个工作，但这些技术被优化以用于解决不同的问题。当试图解决企业数据集成问题时，缺乏一个选择了适当技术的可靠架构可能是失败的原因。

2.3 经济挑战

之前提到的公司和技术相关问题一起使得数据集成成为所有数据仓库/商业智能项目的最昂贵部分。增加数据集成成本的主要因素有：
• 将数据转换为数据集成所需要的格式是一个与公司能力相关的缓慢而痛苦的过程。

• 清理数据和将数据从多数据源匹配到一个一致的、有意义的格式是极其困难的。

• 标准数据集成工具往往不提供足够的功能或可扩展性来满足项目对数据转换的要求。这可能导致要花费大量的咨询成本来开发特殊的ETL代码以使得工作得以完成。

• 公司的不同部门只能关注到局部的数据集成问题。

当需要将它们集合到一起时，需要花费额外的努力将这些成就集成到一个企业级的数据集成架构中。

因为公司的数据仓储和商业智能需要不断发展，不完善的数据集成架构变得越来越难以操纵，并且公司花费的总成本越来越高。

3. SQL Server 2008集成服务

传统的从标准数据源进行的ETL为中心的数据集成仍然是大多数数据仓库的中心。然而，要包括更多样数据源、调整要求以及全球和在线操作的需求在快速地改变传统的数据集成要求。在这个快速发展和改变的前景下，从数据提取值的需求以及可以依赖于它的需求变得比之前任何时候都重要。高效的数据集成成为了高效率决策制定的基础。SQL Server 集成服务提供了一个灵活的、快速的、和可扩展的架构，它使得可以在当前的商业环境中进行高效的数据集成。

在本篇文章里，我们将看看SQL Server 集成服务(SSIS)对于传统的ETL操作需求和一般目的数据集成不断发展的需求是怎样的一个有效工具集。我们还将讨论SSIS怎样从根本上与主要ETL供应商所提供的工具和解决方案的不同，以至于它能够很好的满足全球商业从最大规模的企业到最小规模企业不断变化的需求。

3.1 SSIS架构

3.1.1 任务流和数据流引擎

SSIS包含一个面向操作的工作流引擎，以及一个可扩展的和快速的数据流引擎。这个数据流存在于一个完整工作流的上下文中。工作流引擎提供了运行时资源和对数据流引擎的操作支持。工作流和数据流的结合使得SSIS在传统的ETL或数据仓库(DW)场景，还有许多其它延伸的场景例如数据中心场景中非常有效。在这篇文章里，我们将主要讨论数据流相关的场景。

3.1.2 管道架构

SSIS的核心是数据转换管道。这个管道具有一个面向缓冲器的架构，它在操纵数据的记录集时，一旦记录集加载进了内存中，那么它的处理速度就非常快。方法是在一个不分级数据的单独操作里执行ETL过程的所有数据转换步骤，不过特殊的转换、操作需求或者硬件确实可能是一个障碍。但是，对于大多数操作来说，这个架构避免了分级。SSIS甚至尽可能地避免了在内存中复制数据。这是与传统的ETL工具相对比，传统的ETL工具通常需求在几乎每一步的仓库存储和集成过程中都需要进行分级。不需要分级的操纵数据能力超出了传统的关系型和文本文件数据以及超出了传统的ETL转换能力。有了SSIS，所有类型的数据(结构化数据、非结构化数据、XML，等等)在加载进它的缓冲器中之前被转换成一个表格(列和行)结构。你可以应用于表格数据的任何数据操作都可以应用于数据流管道任何步骤中的数据。这意味着一个单独的数据流管道可以集成不同的数据源并不必将这个数据分级就可以对其执行任何的复杂操作。

不过你应该注意到，如果基于业务或操作原因分级是必需的，那么SSIS也可以很好的支持这些执行。

这个架构使你可以在许多数据集成场景中使用SSIS，从传统的面向DW的ETL到非传统的信息集成技术。

3.1.3 ADO.NET连通性

集成服务解决方案的一个重要方面是提取或加载数据。因此，你的集成解决方案可以无缝地连接到一个广泛的数据源以使得可以从一个广泛的数据访问平台获得最大的性能和可靠性收益是非常重要的。SQL Server 2008集成服务对于ADO.NET 连接(先前的版本对于OLE DB或ODBC是最佳的。)来说是最佳的。向ADO.NET 的转移改进了系统集成和第三方支持。SQL Server 2005集成服务将OLE DB 用于重要的任务，例如查找，但是现在你可以将ADO.NET 用于这些任务以及源和目的组件。

3.1.4 线程池

当一个集成解决方案升级后，它通常就很难再提高性能了。SQL Server 2008集成服务打破了这个限制，它在多个组件间共享线程，这增加了并行以及降低了堵塞;并因此增加了大型高并行、多处理器、多内核系统的性能。

而且在大多数系统上提高性能，线程池还会降低对手工配置SSIS包以增加并行的要求，并因此增加了开发人员的生产力。

3.1.5 永久查找

执行查找是集成解决方案中最普遍的一个操作。特别是在真实记录使用查找将商业值转换为它们相应替代的数据仓储中非常普遍。SQL Server 2008集成服务提高了查找的性能，达到可以满足最大规模的数据表的程度。

你可以配置查找转换，将它设置为在输入字段处理之前缓存参考数据的一些、或所有。SQL Server 2008集成服务可以加载一个任何数据源完整的缓存，并允许这个缓存大于4GB，即使是在一个32位的操作系统上。通过使用一个局部缓存，SQL Server 2008集成服务使用数据流预控制查找。局部缓存支持用于数据库查找的OLEDB、ADO.Net和ODBC，而且它们跟踪查找的成功和失败。如果你选择不进行预先缓存参考数据，那么SQL Server 2008集成服务支持批量数据库调用和大小写不敏感的匹配。

3.2 集成场景

3.2.1 SSIS用于数据转换操作

尽管SQL Server 2005集成服务比之前的版本功能更多，产品更强大，但是许多用户发现使用向导进行简单的数据转换却变得更加复杂和功能更少了。SQL Server 2008集成服务具有一个改良的向导、它使用ADO.NET、具有改良的用户界面、执行自动数据类型转换、并且可扩展性比之前任何版本都好。

图2

图1

3.2.2 SSIS用于数据仓库加载

SSIS是一个广泛的、功能完全的ETL工具。它的功能、级别和性能可与市场中的高端竞争者相比较，但价格只相当于它们的一小部分。数据集成管道架构允许它使用同时从多个数据源而来的数据、执行多个复杂的转换，然后将数据一起存储到多个目的地去。这个架构允许SSIS不只用于大型数据集，还可用于复杂的数据流。因为数据流是从数据源到目的地，你可以分割、合并，以及将数据流与其它数据流结合，并进行操纵。图3显示了这样一个数据流的例子。

图3

图1

SQL Server 2008包含对变化数据捕捉(CDC)的支持，你可以使用CDC来记录对SQL Server数据表的插入、更新和删除活动，并且使这个变化细节可以以一种简单使用的关系型格式来使用。当使用SQL Server 2008集成服务来执行一个ETL解决方案用以确保只有变化的数据被包括进提取过程时，你可以利用CDC，这使得不会执行在每个ETL操作里包含未改变数据的全部数据刷新。

SSIS通过使用一组专门的叫做适配器的组件使得可以使用从许多数据源获得的数据，包括管理的(ADO.NET)、OLE DB、ODBC 、文本文件、Microsoft Office Excel®以及XML。SSIS甚至可以使用从定制数据适配器获得的数据(由内部开发的或第三方开发的)，所以你可以将遗留数据加载逻辑封装进一个数据源，然后你可以将它无缝地集成进SSIS数据流。SSIS包含一组强大的数据转换组件，它们允许进行对于建立数据仓库来说非常重要的数据操纵。这些转换组件包括：

· 聚合。在一个单独的传递里执行多个聚合。

· 分类。在流里分类数据。

· 查找。执行灵活的查找操作来参考数据集。

· Pivot和UnPivot。流里面两个单独的转换透视和逆透视数据。

· Merge、Merge Join和UnionAll。可以执行join和union操作。

· 派生列(Derived Column)。 执行列级别的操纵，例如string、numeric和date/time 操作，并编码进行页面转化。这个组件封装了其它供应商可能会分解成许多不同转化的东西。

· 数据转换。在多种类型之间转换数据(例如numeric和string)。

· 审查。Audit. 用元数据和其它操作的审查数据添加字段。

除了这些主要的数据仓库转换，SSIS还包括对高级数据仓库需求的支持，例如缓慢变化维(SCD)。在SSIS中的SCD向导指导用户指定他们对管理缓慢变化维的需求，并且基于他们的输入，生成一个具有多转换的数据流来执行缓慢变化维加载。提供了对标准类型1和2SCD以及两个新的SCD类型(Fixed Attributes和Inferred Members)的支持。图4显示了一个SCD向导的页面。

图4

图1

图5显示了这个向导产生的数据流。

图5

图1

你还可以使用SSIS直接从数据流管道加载分析服务多维OLAP(MOLAP)缓存。这意味着你不只可以使用SSIS来创建关系型数据仓库，而且你还可以用它来加载多维立方体用来分析应用程序。

3.2.3 SSIS和数据质量

SSIS的一个主要特性，以及它的集成数据的能力，是它集成不同技术来操纵数据的能力。这使得SSIS包含了创新的基于“模糊逻辑”的数据清理组件。Microsoft研究实验室开发了这些组件，并且他们展示了在这个领域的最新研究。采用的方法是一个独立的域而且不依赖于任何特定的域数据，例如地址/邮编参考数据。这使得你可以将这些转换用于清理大多数类型的数据，而不仅仅是地址数据。

SSIS与分析服务的数据挖掘功能深度集成。数据挖掘抽取了数据集的格式并将它们封装到一个挖掘模型里。然后你可以使用这个挖掘模型来预测一个数据集包含什么数据以及什么数据是反常的。这样你就可以将数据挖掘作为一个实现数据质量的工具。

在SSIS中对复杂数据路由的支持不仅帮助你确认异常数据，而且还可以自动修改并用更好的值来替代它。这使得“闭环”清理场景可用。图6显示了一个闭环清理数据流的例子。

图6

图1

除了它的内置数据质量特性，SSIS还可以扩展为与第三方数据清理解决方案紧密协作。

3.2.4 超出传统ETL的SSIS应用程序

数据流管道操纵几乎任何类型数据的能力、与分析服务的深度集成、对使用许多数据操纵技术对它进行扩展的支持，以及包含了一个丰富的工作流引擎，这些使得SSIS可以用在很多不是传统的ETL场景中。

服务面向架构

SSIS包括对数据流管道中源XML数据的支持，包括从磁盘上文件中获得的数据以及通过HTTP的URL。XML数据是“粉碎”放到表格数据中的，然后它就可以在数据流中很容易地进行操纵。这个对XML的支持可以与对Web services 的支持一起使用。SSIS可以与控制流中的Web services进行交互以捕捉XML数据。

你可以从文件中、从Microsoft Message Queuing(MSMQ)中，以及通过HTTP从Web捕捉XML。SSIS使得可以操纵具有XSLT、XPATH、diff/merge、等等的 XML。并且可以将这个XML放到数据流中。

这个支持使得SSIS可以成为灵活的服务面向架构(SOA)的一部分。.

数据和文本挖掘

SSIS不只具有与分析服务数据挖掘功能的深度集成，它还具有文本挖掘组件。文本挖掘(也被成为文本分类)围绕确定商业种类和文本数据(单词和短语)之间的关系。这使得可以发现文本数据中的关键术语，并且基于此，自动地确认“感兴趣”的文本。这反过来可以驱使“闭环”动作以完成商业目的，例如增加客户满意度和增强产品和服务的质量。.

按需数据源

SSIS中的一个特有功能是DataReader 目的地，它将数据放到一个ADO.NET DataReader 中。当这个组件被包含进一个数据流管道中时，你可以使用包含了DataReader 目的地作为一个数据源的包，它作为ADO.NET DataReader 来显示。所以你可以将SSIS不仅作为一个传统的ETL来加载数据仓库，还可以作为一个可以按需地从多个数据源发送集成的、一致的和干净数据的数据源。例如，你可以通过使用一个SSIS包作为报表服务的数据源，从而帮助它使用从多个不同数据源而来的数据。

一个集成所有这些特性的可能场景包含从RSS feeds确认和发送感兴趣的文章作为一个常规报表的一部分。图7显示了一个SSIS包通过Internet 获得从RSS feeds而来的源数据，与从Web service 而来的数据相集成，执行文本挖掘从RSS feeds找到感兴趣的文章，然后将感兴趣的文章放到一个DataReader目的地中去，最后由一个报表服务的报表使用。

图7

图1

图8显示了在Report Wizard里SSIS包作为一个数据源来使用。

图8

图1

在ETL工具透视图里，这个场景是非常不寻常的，因为没有数据提取、转换或加载。

3.2.5 SSIS，集成平台

SSIS超出了一个ETL工具的范畴，不只在使得非传统场景可用方面，还因为它是一个用于数据集成的真正平台。SSIS是SQL Server 商业智能(BI)平台的一部分，它使得开发端对端商业智能应用程序成为可能。

集成的开发平台

SQL Server 集成服务、分析服务，以及报表服务都使用一个基于通用的Microsoft Visual Studio®的开发环境，叫做SQL Server 商业智能(BI)开发套件。商业智能开发套件提供了一个用于商业智能应用程序开发的集成开发环境(IDE)。这个共享的基础构造使得可以在多个开发项目(集成、分析、和报表)之间进行元数据级别的集成。这个共享构造的一个例子是数据源视图(DSV)，它是一个脱机的数据源schema/view定义，并被所有的三种商业智能项目类型所使用。

这个IDE提供了与其它软件集成的工具，例如版本控制软件(例如VSS)，以及对基于团队的特性例如“check-in/check-out”的支持，而因此它满足了用于商业智能应用程序的企业级面向团队开发环境的需求。图9显示了一个商业智能开发套件解决方案，它包含了集成、分析和报表项目

图9

图1

它不只提供了一个单独的环境用于开发商业智能应用程序，它还用于开发其它的Visual Studio 项目(使用Visual C#®、Visual Basic® .NET等等)，并因此可以提供给开发人员一个真实的端对端开发体验。

除了集成的商业智能开发环境，商业智能开发套件还具有用于真实的运行时调试SSIS包的功能。这些包括了设置检查点和支持标准开发构造的能力，例如观察变量。一个真正的特有功能是Data Viewer，它提供了在数据流管道中集成服务处理数据记录时查看这些数据记录的能力。这个可视化的数据可以是常规的文本网格形式或一个图表展示，例如一个扩散图或条图。事实上，可以有多个连接的阅览器，它们可以同时地以多种格式显示数据。图10显示了一个使用扩散图和文本网格可视的地理数据例子。

图10

图1

可编程性

除了提供一个专业的开发环境，SSIS还通过一组丰富的API显示了它所有的功能。这些API都是可管理的(.NET框架)和本地的(Win32)，并且允许开发人员通过使用.NET框架所支持的语言(例如Visual C#、Visual Basic .NET等等)和Visual C++开发定制的组件扩展SSIS功能。这些定制的组件可以是工作流任务和数据流转换(包括源和目的地适配器)。这使得遗留数据和功能可以简单地包括进SSIS集成过程，使你可以有效地使用遗留技术中过去的投入。它还使得可以简单地包含第三方组件。

脚本

先前提到的可扩展性不只局限于可重用的定制组件，还包括了基于脚本的可扩展性。SSIS具有用于工作流和数据流的脚本组件。这些使得用户可以用Visual Basic. NET编写脚本来添加热添加功能(包括数据源和目的地)，以及重用任何先前存在的、打包为.NET框架装配的功能。

SQL Server 2008包括Visual Studio Tools for Applications，它提供了一个脚本环境，在这个环境里，你可以使用Visual Basic .NET或C#来执行脚本组件。

图11显示了一个脚本例子，它检查了一个Office Excel 文件是否存在。

图11

图1

这个可扩展性模型使得SSIS不仅仅是一个数据集成工具，还是一个集成总线，像数据挖掘、文本挖掘和统一维度模型(Unified Dimensional Model，UDM)这样的技术可以简单地插入其中，使得可以使用围绕许多任意数据操纵和结构的复杂集成场景。

4、使得数据集成易于得到

SSIS的灵活的、可扩展的架构使它可以解决这篇文章之前提到的数据集成面临的大多数技术挑战。如图12所示，SSIS消除了(或至少降为最低)不必要的分级。因为它在一个单独的管道操作中执行复杂的数据操纵，它现在可以在一个对结束循环和采取行动很有用的时间框架中对数据的变更和格式快速反应。这是与依赖于数据分级的传统架构相比，传统架构不能结束循环和对数据采取有效行动。

图12

图1

SSIS可扩展的本质使得公司可以利用它们已有的对定制数据集成代码的投入，只要将它封装为SSIS可重用的扩展，这样就可以充分利用这些功能，例如日志记录、调试、商业智能集成，等等。这极大地帮助解决一些文章中先前提到的公司挑战。

与其它高端数据集成工具相比，在SQL Server 产品中包含SSIS使得它的价格非常合理。不只是原始成本降低了，而且通过与Visual Studio和其余SQL Server商业智能工具的紧密集成，应用程序开发和维护的成本与其它类似工具相比显著降低了。SSIS(和SQL Server的其它部分)非常合理的总体拥有成本(TCO)使得企业级数据集成对于市场的所有层级都是易于得到的，不再是那些大型(富有)公司的独占领域。同时，SSIS的架构变为利用现代硬件和提供性能以及扩展为可满足最高端的客户需求。SSIS使得丰富的、可扩展的数据集成提供给所有客户，从最高端的企业到中小企业。与SQL Server中的其它功能联合起来，Microsoft客户支持基础构造(从宽广的测试，到丰富的在线团体到突出的支持合同)和一致性以及与Microsoft产品提供的其它部分的集成，SSIS真的是唯一打开了数据集成新边界的工具集。

5、总结

在现今的商业世界中，许多公司依赖于数据集成技术提供有用的、可靠的信息来维护竞争优势。SQL Server 2008集成服务(SSIS)帮助信息技术部门来满足他们公司对数据集成的要求。SQL Server 2008集成服务满足了清理、转换和匹配大型的多个数据源到可用格式的要求。新特性改进了它的能力来升级和提高性能，同时加快了开发速度和降低了TCO。