SQL Server 2005 集成服务简介

Kamal Hathi

简介

要在当今的商务世界中获得竞争优势，将公司的数据转换为有意义且可操作的信息是唯一最为重要的竞争优势源。充分利用数据爆炸，以更好地了解过去业务状况，洞悉未来的发展方向，这已经成为全球组织中的企业 IT 部门所面临的最有挑战性的任务之一。与数据集成相关的问题可分为三大类：

·         技术挑战

·         组织问题

·         经济挑战

在本文中，我们将深入了解这些挑战的细节，并讨论如何使用 Microsoft® SQL Server™ 2005 集成服务 (SSIS) 应对这些挑战。首先，让我们在实际的场景中了解这些挑战。

实际场景

一家全球主要的运输公司使用数据仓库来分析其运营绩效和预测其计划运输任务中的偏差。

数据源

该公司的主要数据源包括来自其基于 DB2 的订单项系统的订单数据、来自其基于 SQL Server 的客户关系管理 (CRM) 系统的客户数据以及来自其基于 Oracle 的 ERP 系统的供应商数据。除了来自这些主要系统的数据之外，也将来自跟踪“特别”事件的电子表格的数据（由运输管理人员手动输入）合并到了数据仓库中。目前，天气信息、交通状况和供应商详细信息（针对转包的运输任务）等外部数据以延迟的方式从各种源以文本文件格式合并到数据仓库中。

数据使用

不仅这些数据的源多种多样，而且使用者的要求和地理位置也各不相同。这个多样性导致了本地系统的增加。IT 部门的主要工作之一就是至少为其客户数据建立“事实的单一版本”。

数据集成要求

考虑到数据、业务需求以及用户要求的这个多样性，IT 部门提出了下面一组数据集成要求：

·         必须提供可靠一致的历史数据和当前数据，这些数据从各种外部源和内部源集成。

·         为了数据获取过程中的延迟，必须能通过 Web 服务和其他直接机制（如 FTP）使用来自提供商和供应商的数据。

·         需要清理和移除重复数据等，以保证数据质量。

·         增加全局管理要求需要公司维护清楚的审计跟踪。维护可靠的数据并不够，还需要对数据进行跟踪和认证。

数据集成挑战

就某种意义而言，我们所给出的实际场景非常简单。从多个源获取数据，清理并转换数据，然后将该数据加载到适当的数据存储区以进行分析和报告。不过，在典型的数据仓库和商业智能项目中，企业将 60–80% 的资源都用在了数据集成阶段。为什么这么困难呢？

技术挑战

技术挑战首先存在于源系统。我们正将在事务（客户在此进行提交以获得、购买或获取商品）上收集数据变成在预事务（在此通过 Web 点击或 RFID 等机制跟踪客户的意图）上收集数据。现在不仅仅只通过传统源和格式（如数据库和文本文件）获取数据，而且可以使用各种不同的格式（从专用文件到 Microsoft Office 文档再到基于 XML 的文件等），且能从各种基于 Internet 的源获取，如 Web 服务和 RSS (Really Simple Syndication) 流。最有关系的挑战包括：

·         采用不同格式的多个源。

·         结构化、半结构化和非结构化的数据。

·         来自源系统的数据并不同时到达。

·         海量数据。

在理想的情况下，即使我们勉强将所需的所有数据均放置到了一个位置，各种新挑战仍然会浮出水面，包括：

·         数据质量。

·         理解不同的数据格式。

·         将数据转换为对业务分析人员有意义的格式。

假定我们可以获得所需的全部数据，并可以进行清理、转换并将数据转换为有用的格式。仍然会有一点与传统数据移动和集成不同。就是将从固定长度的面向批处理的过程转换到流线型的、更短的按需过程。面向批处理的过程通常在“低谷”期间（此时用户没有繁重的系统需求）执行。这通过在夜间 6-8 小时的预定义批处理窗口工作，此时应该不会有人在办公室。随着各种规模和类型的企业全球化进程，这已不再适合了。低谷时间很少（如果有的话），在世界的某个地方总有人在办公室。在全球化企业中，真的是“日不落”。

由此会有如下效果：

·         尽可能快速加载数据的压力越来越大。

·         需要同时加载多个目标。

·         目标多样化。

我们不仅需要完成所有这些工作，而且需要尽可能快地完成操作。在极端的情况下（如联机业务），需要持续地进行数据集成。实际中并没有少于数分钟的批处理窗口和延迟。在很多情况下，都使用持续运行的软件来实现决策过程的自动化。

由于业务需求不能有任何的停机时间，所以可扩展性和性能变得越来越重要了。

如果没有采用正确的技术，系统几乎会要求在数据仓库处理和集成过程的每一步都进行暂存。随着需要在 ETL（提取、转换和加载）过程中包括不同的（特别是非标准的）数据源，以及需要对数据进行更为复杂的操作（如数据和文本挖掘），对数据进行暂存的需求也开始增加。如图 1 中所示，随着数据暂存增加，“关闭循环”（即对新数据分析和进行操作）的时间也会增加。这些传统的 ELT 结构（与加载前出现的增值 ETL 过程相对）对系统响应新兴业务需求的能力施加了严格的限制。

图 1

最后，当需要应用程序集成的实时事务技术和面向批处理的数据集成技术领域的各种技术解决企业的业务问题时，数据集成如何与组织的总体集成结构相结合的问题变得越来越重要了。

组织挑战

大型组织中有与数据集成相关的两大问题；分别为“动力”问题和“适宜”问题。

动力挑战：数据就是动力，要使人们将数据看作公司真正有价值的共享资产通常非常困难。为了成功实现企业数据集成，多个数据源的所有者必须完全根据项目的目的和方向进行购置。缺乏相关方的协作是数据集成项目失败的一个主要原因。专用资金投入、一致的构建以及有多个股东的出色数据集成团队都是可以帮助解决此问题的关键成功因素。

适宜挑战当按照独立的需求进行分析时，可以采用多种方式解决数据集成问题。约 60% 的数据集成可通过手动编码解决。用于解决类似问题的技术包括从复制、ETL、SQL 到 EAI 的各种技术。人们趋向于使用其熟悉的技术。尽管这些方法功能方面有所重复，可能可以在各种独立的情况下完成任务，但这些技术均经过了优化，以解决不同的系列问题。当试图解决企业数据集成问题时，缺乏具有恰当技术选择的良好结构可能导致失败。

经济挑战

前面列出的与组织和技术相关的问题结合起来，可能会导致数据集成变成任何数据仓库/商业智能项目中最为昂贵的部分。导致数据集成开销增加的主要因素包括：

·         将数据转换为数据集成所必需的格式，可能会成为伴随组织数据动力游戏始终的缓慢而单调的过程。

·         对来自多个源的数据进行清理，并将其映射到一个一致而有意义的格式会非常困难。

·         很多时候，标准数据集成工具并不提供足够的功能或扩展性以满足项目的数据转换要求。这会导致咨询成本（为了开发特殊的 ETL 代码而完成操作）方面大量的资金支出。

·         组织的不同部分关注各自相关的数据集成问题。当需要全面集成时，将需要额外的成本以将这些成果集成到企业级的数据集成结构中。

由于组织的数据仓库和商业智能需求的原因，错误数据集成结构变得越来越难于维护，总体拥有成本也会直线上升。

SQL Server 2005 集成服务

传统的以 ETL 为中心的标准数据源数据集成将继续处于大部分数据仓库核心的重要地位。不过，由于包括更多不同类型的数据源的要求、管理要求以及全球化和联机操作的原因，数据集成的传统要求正迅速地发生着变化。在这个快速发展、不断变化的环境中，从数据获得价值和数据可靠性的需求比以往任何时候都要重要。有效的数据集成已成为进行有效决策的基础。SQL Server 集成服务提供了灵活快速且可扩展的结构，可以在当前业务环境中进行有效的数据集成。

在本文中，我们将了解 SQL Server 集成服务 (SSIS) 如何同时有效满足 ETL 操作的传统要求和通用数据集成不断发展的需求。我们还将讨论 SSIS 与主要 ETL 供应商提供的工具和解决方案有什么本质的差别，这些差别使其成为可满足各种规模的全球企业（从最大的企业到最小的公司）不断变化的要求的理想解决方案。

SSIS 结构

任务流和数据流引擎

SSIS 由面向操作的任务流引擎和可扩展的快速数据流引擎组成。数据流存在于整体任务流的上下文中。任务流引擎为数据流引擎提供运行时资源和操作支持。任务流和数据流的这一组合使得 SSIS 可以有效应用于传统 ETL 或数据仓库 (DW) 场景中，也可有效应用于很多其他扩展场景中，如数据中心操作。在本文中，我们将主要关注与数据流相关的场景。面向数据中心的工作流的使用本身就是一个独立的主题。

管线结构

SSIS 的核心是数据转换管线。此管线具有面向缓冲区的结构，一旦数据行集加载到内存中后，数据行集操作将非常快。采用此方法将在单个操作中执行 ETL 过程的全部数据转换步骤，而不会对数据进行暂存，不过具体的转换或操作要求或者硬件可能对此形成障碍。不过，为了最大化性能，该结构将避免进行暂存。甚至也尽可能避免在内存中进行数据复制。这与传统 ETL 工具刚好相反，传统的工具通常几乎需要在仓库操作和集成过程的每个步骤进行暂存。无需进行暂存即可操作数据的功能实现了扩展，突破了传统的关系数据和纯文本文件数据以及传统 ETL 转换功能的限制。使用 SSIS，所有类型的数据（结构化的、非结构化的、XML 等）均在加载到其缓冲区前转换为表（列和行）结构。任何可应用到表数据的数据操作均可在数据流管线的任何步骤应用到数据。这意味着单个数据流管线可以集成各种数据源的数据，可以对这些数据进行任意复杂操作，均不需对数据进行暂存。

还应该注意，如果由于业务或操作原因需要进行暂存，SSIS 也提供对这些实现的良好支持。

此结构允许将 SSIS 在各种数据集成场景中使用，包括从传统的面向 DW 的 ETL 到非传统的信息集成技术等。

集成场景

将 SSIS 用于传统 DW 加载

就实质而言，SSIS 是一个综合的全功能 ETL 工具。其功能、规模和性能可与市场上的高端竞争产品一争高下，而其价格远远低于这些产品的价格。数据集成管线结构允许其使用来自多个平行源的数据，执行多个复杂的转换，然后将数据发送到多个平行目标。此结构不仅允许将 SSIS 用于大型数据库，还可以用于复杂数据流。在将来自源的数据传送到目标位置的过程中，可以将数据流拆分、合并、与其他数据流组合，以及进行其他操作。图 2 所示为此类流的示例：

图 2

SSIS 可以通过称为适配器的专用组件集而使用来自各种源的数据（也可将数据发送到这些位置），包括 OLE DB、托管 (ADO.NET)、ODBC、纯文本文件、Excel 和 XML。SSIS 甚至可以使用来自自定义数据适配器（由内部或第三方开发）的数据。这允许将旧式数据加载逻辑包装为可以在 SSIS 数据流中无缝使用的数据源。SSIS 包括了一组强大的数据转换组件，允许进行构建数据仓库所需的主要数据操作。这些转换组件包括：

·         Aggregate  在单次传递中执行多次聚合。

·         Sort  对流中的数据进行排序。

·         Lookup  执行灵活的缓存查询操作以引用数据集。

·         Pivot 和 UnPivot  两个独立的转换，分别执行其名称所指的操作。

·         Merge、Merge Join 和 Union All  执行连接和联合操作。

·         Derived Column  执行列级别的操作（如字符串、数字、日期/时间等操作）和代码页转换。该组件对其他供应商拆分为许多不同转换的内容进行包装。

·         Data Conversion  在各种类型（数字、字符串等）之间进行数据转换。

·         Audit  添加具有线性元数据和其他操作审计数据的列。

除了这些核心数据仓库转换之外，SSIS 还包括对高级数据仓库需求的支持，如慢速更改维 (SCD)。SSIS 中的 SCD 向导将根据用户输入指定其管理慢速更改维的要求，从而给用户提供指导，该向导将使用多个转换生成完整的数据流，以实现慢速更改维加载。提供标准 Type 1 和 2 SCD 支持以及 2 种新 SCD 类型（固定属性和推导成员）支持。图 3 所示为 SCD 向导中的一个页面。

图 3

图 4 所示为由此向导生成的数据流。

图 4

SSIS 还可用于直接从数据流管线加载分析服务多维 OLAP (MOLAP) 缓存。这意味着 SSIS 不仅可以用于创建管线数据仓库，还可以用于为分析应用程序加载多维数据集。

SSIS 与数据质量

SSIS 的主要功能之一就是其不仅具有集成数据的功能，而且可以集成不同的技术以对数据进行操作。这使得 SSIS 可以包括基于先进的“模糊逻辑”的数据清理组件。这些组件由 Microsoft Research 实验室开发，代表了此领域最新的研究成果。该方法依赖于域，并不依赖于任何具体的域数据，如地址/邮编引用数据。这允许将这些转换用于清理大部分类型的数据，而不只是地址数据。

SSIS 与分析服务中的数据挖掘功能实现了深度集成。数据挖掘对数据集中的模式进行抽象，并将其封装在挖掘模型中。然后可以将此挖掘模型和其他内容用于对哪些数据属于数据集和哪些数据可能是不规则的进行预测，从而允许将数据挖掘作为实现数据质量的工具。

SSIS 中对于复杂数据路由的支持不仅允许对不规则数据进行标识，并能自动使用更好的值进行更正和替换。这支持“关闭循环”清理场景。图 5 所示为此类关闭循环清理数据流的示例。

图 5

除了其内置的数据质量功能外，SSIS 还能进行扩展，以与第三方数据清理解决方案密切协作。

传统 ETL 之外的 SSIS 应用

SSIS 的数据流管线可以操作任何类型的数据，其与分析服务实现了深度集成，支持使用各种数据操作技术对其进行扩展，并包括了丰富的工作流引擎，这些使得 SSIS 可以在很多采用非传统 ETL 思想的场景中使用。

面向服务的结构

SSIS 包括对数据流管线中的源 XML 数据的支持，包括来自磁盘上的文件和 HTTP 上 URL 的数据。XML 数据将被“剪切”为表数据，然后就可以在数据流中方便地操作此表数据。这个对 XML 的支持可以与对 Web 服务的支持结合使用。SSIS 可以与控制流中的 Web 服务交互，以捕获 XML 数据。

还可以从文件、Microsoft 消息队列 (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 上的相关文章）的可能场景是日常报告的一部分。图 6 所示为一个 SSIS 数据包，该数据包将来自 Internet 上 RSS 源的数据作为源，与 Web 服务数据集成，执行文本挖掘以从 RSS 源找到相关的文章，然后将相关文章发送到 DataReader 目标以最终由报告服务报告使用。

图 6

图 7 所示为在报告向导中将 SSIS 数据包作为数据源使用的例子。

图 7

从 ETL 工具的角度出发，此场景非常少见，因为并没有任何数据提取、转换和加载。

SSIS 集成平台

SSIS 从支持非传统场景和成为真正的数据集成平台方面而言，均不仅是一个 ETL 工具。SSIS 属于 SQL Server Business Intelligence (BI) 平台，该平台支持端到端 BI 应用程序的开发。

集成开发平台

SQL Server 集成服务、分析服务和报告服务均使用共同的基于 Visual Studio® 的开发环境，名为 SQL Server Business Intelligence (BI) Development Studio。BI Development Studio 为 BI 应用程序开发提供了集成开发环境 (IDE)。此共享的基础结构支持各种开发项目（集成、分析和报告）间的元数据级集成。此类共享构造的例子之一就是源数据视图 (DSV)，这是数据源的脱机架构/视图定义，所有三种 BI 项目类型均会使用。

此 IDE 提供了各种机制，如与版本控制软件（如 VSS）集成，还提供了对基于团队功能的支持（如“签入/签出”），以满足用于商业智能应用程序的面向团队的企业级开发环境的需求。图 8 所示为 BI Development Studio 解决方案，该解决方案由集成、分析以及报告项目组成。

图 8

这不仅提供了单个位置以开发 BI 应用程序，但也可以用于开发其他 Visual Studio 项目（使用 Visual C#、Visual Basic .NET 等），因此可以向开发人员提供真正的端到端开发体验。

除了集成的 BI 开发环境，BI Development Studio 还具有供 SSIS 数据包的真正运行时调试使用的各种功能。这包括设置断点的功能和对标准开发构造（如监视变量）的支持。真正的独特功能是数据查看器，该工具提供了在数据流管线中处理数据行时查看数据行的能力。此数据的虚拟化可以采用规则文本网格的形式，也可以采用图形表示形式（如散点图和柱状图）。事实上，可以拥有多个连接的查看器，这些查看器可以同时以多种形式显式数据。图 9 所示为使用散点图和文本网格可视化的地理数据示例。

图 9

可编程能力

除了提供专业开发环境外，SSIS 还将其所有功能通过一组丰富的 API 公开。这些 API 既是托管 API (.NET Framework)，也是本机 API (Win32)，允许开发人员通过采用 .NET Framework 支持的任何语言（如 Visual C#、Visual Basic .NET 等）和 C++ 开发自定义组件，以扩展 SSIS 的功能。这些自定义组件可以是工作流任务，也可以是数据流转换（包括源适配器和目标适配器）。利用这一点，可以方便地在 SSIS 集成过程中将旧式数据和功能包括进来，允许有效地利用过去的旧式技术的投资。它还允许轻松地将第三方组件包括进来。

脚本

前面提到的扩展性不仅限于可重用的自定义组件，也包括基于脚本的扩展性。SSIS 同时具有用于任务流和数据流的脚本组件。这些使得用户可以用 Visual Basic. NET 编写脚本以添加临时功能（包括数据源和数据目标），并可以重用任何打包为 .NET Framework 程序集的预先存在的功能。

图 10 所示是一个脚本示例，该脚本对数据流中的数据行进行操作。

图 10

此扩展性模型使得 SSIS 不但是一款数据集成工具，同时也是一个集成总线，可以方便地将数据挖掘、文本挖掘和 UDM 插入到其中，以支持复杂的集成场景（其中会涉及到相当多的任意数据操作和结构）。

使数据集成变为现实

SSIS 的灵活且可扩展的结构使其可以应付本文前面给出的大部分数据集成技术挑战。如图 11 中所示，SSIS 消除了（或至少最小化了）不必要的暂存。由于它在单个管线操作中执行复杂的数据操作，现在可以相当快地对数据中的更改和模式作出响应，所耗时间对关闭循环和进行操作都有实际意义。这与传统结构相反，传统结构依赖于数据暂存，而这对于关闭循环和对数据进行有意义的操作变得不甚现实。

图 11

SSIS 的可扩展本性使组织可以通过将数据集成自定义代码作为可重用的 SISS 扩展包装，并通过这样充分利用日志记录、调用、BI 集成等，从而达到对其在此方面的现有投资的充分利用。这将很大程度上帮助应付本文早些时候列出的一些组织挑战。

将 SSIS 包括在 SQL Server 产品中，使得成本开销与其他高端数据集成工具相比显得非常合理。与其他类似产品相比，不仅初始购买成本更低，而且通过与 Visual Studio 和其他 SQL Server BI 工具的紧密集成，应用程序开发与维护成本也得到大幅度降低。SSIS （以及 SQL Server 的其他部分）具有非常合理的总体拥有成本 (TCO)，允许整个市场均能实现企业级数据集成，使其不再是各种大型（同时也是最富有的）公司独享的专权。同时，SSIS 的结构经过了优化，可以充分利用现在的硬件，提供能满足最高端的客户要求的性能和规模。SSIS 可为所有客户（从最高端的企业到中小型企业）提供丰富的可扩展数据集成。SISS 与 SQL Server 中的其他功能、Microsoft 客户支持基础结构（从广泛而长期的预测试到丰富的在线社区，再到初始支持合同）结合，具有与其他 Microsoft 产品服务的一致性并实现了紧密集成，是真正独特的工具集，在数据集成领域开辟了一片新天地。