SSIS 概述 - 第一部分

引言

当我开始从事 SSIS 工作时，我甚至不知道 SSIS 是什么以及它有什么作用。所以，我开始阅读并为它的所有功能做简短的笔记。在本文中，我将解释一些重要的定义和处理流程。在接下来的部分中，我将详细介绍每个控件及其配置。在此，我将重点介绍 SSIS 的架构、行为和重要组成部分。我提供的内容来自“MSDN”和“Pro SQL Server 2005 Integration Services”。

SSIS 概述

SQL Server Integration Services (SSIS) 是一个用于构建高性能数据集成和工作流解决方案的平台。它允许创建由任务组成的包或 SSIS 包，这些任务可以将数据从源移动到目标，并在需要时对其进行修改。SSIS 本质上是一个 ETL（提取、转换和加载）工具，其主要目的是执行数据的提取、转换和加载，但它也可用于其他多种目的，例如，自动化 SQL Server 数据库的维护、更新多维数据集数据或发送详细说明用户定义的 the operation 状态的电子邮件。SSIS 是 SQL Server 2005/2008 的一个组件，是 DTS（Data Transformation Services）的继任者，DTS 曾用于 SQL Server 7.0/2000。

集成服务的典型用途

• 合并来自异构数据存储的数据
• 填充数据仓库和数据集市
• 清理和标准化数据
• 将商业智能构建到数据转换过程中
• 自动化管理功能和数据加载

词汇表

包

包是层级的根。尽管 SSIS 解决方案可以包含多个包，但它们在逻辑上或物理上都不保存在任何类型的容器中。

容器

包下一级可以是容器或某种可执行文件（控制流可执行文件）。容器是一个逻辑构造，可以包含其他容器或分组在“超级容器”下的组件。

可执行文件

实现必要 SSIS 接口并使其可供 SSIS 使用的组件。例如，脚本组件支持在包中使用 Visual Basic.NET 代码处理包含的数据流。

变量

SSIS 为任何容器或包的一部分提供强类型部分 — 公共类型系统 (CTS) — 兼容变量的支持。

范围

作用域是指变量对代码片段的可见性和可用性，这取决于变量在代码中的定义位置。例如，全局作用域的变量在包级别定义，对下面的任何包、容器或组件都可见且可用。相比之下，在数据流任务级别定义的变量仅对该任务及其子任务可见。

约束条件

控制流设计器可能有多个控制流项，在这种情况下，我们需要决定哪个项会先执行。使用约束条件，我们可以为控制流项定义处理流程。我们可以根据任何项的成功或失败来定义不同的流程。

事件处理程序

在包层级的绝大多数级别都提供了事件处理程序。事件处理程序的类型取决于你要处理事件的对象类型。

SSIS 架构

Microsoft SQL Server Integration Services (SSIS) 包含四个关键部分

• SSIS 服务
• SSIS 对象模型
• SSIS 运行时引擎和运行时可执行文件
• SSIS 数据流引擎和数据流组件

集成服务服务

• 监视正在运行的集成服务包并管理包的存储
• 集成服务对象模型
• 包括访问的本地和托管应用程序编程接口 (API)
• 集成服务工具、命令行实用程序和自定义应用程序

SSIS 运行时引擎和可执行文件

• 运行包
• 支持日志记录、调试、配置、连接和事务
• SSIS 运行时可执行文件
• 包、容器、任务和事件处理程序

SSIS 数据流引擎和组件

• 提供内存缓冲区来移动数据
• 调用数据源适配器以连接到文件和数据库
• 提供转换来修改数据
• 目标适配器以将数据加载到数据存储中
• Components
• 数据源、目标适配器和转换

SQL Server Business Intelligence Development Studio

SQL Server Business Intelligence Development Studio (BIDS) 允许用户通过拖放用户界面创建/编辑 SSIS 包。BIDS 非常用户友好，允许您拖放功能。在单个包中定义工作流的各种元素。在包执行后，该工具提供彩色编码的实时监控。

SSIS 包的组件包括

• 控制流
• 数据流

控制流

控制流处理任务的有序执行，任务是执行特定操作并以有限结果（可评估为成功、失败或完成）结束的独立、隔离的工作单元。虽然它们的顺序可以通过将它们与约束条件链接成任意排列、使用容器将它们分组在一起或在循环中重复执行来定制，但后续任务不会启动，除非其前置任务已完成。

控制流的元素包括

容器

容器在包中提供结构，并为控制流中的任务提供服务。集成服务包括以下容器类型，用于分组任务和实现重复控制流

• Foreach 循环容器：它枚举集合，并为集合中的每个成员重复其控制流。Foreach 循环容器适用于您拥有一个项目集合并希望使用其中的每个项目作为下游流程的某种输入的情况。
• For 循环容器：它是一个基本的容器，提供循环功能。For 循环包含一个计数器，该计数器通常会递增（尽管有时会递减），然后与常量值进行比较。如果条件评估为True，则继续执行循环。
• 序列容器：一种在概念上和物理上都可以容纳任何其他类型容器或控制流组件的特殊容器。它也被称为“容器容器”或“超级容器”。

Tasks（任务）

任务在包中执行工作。集成服务包括用于执行各种功能的任务。

• 数据流任务：它定义和运行数据流，这些数据流提取数据、应用转换并加载数据。
• 数据准备任务：它复制文件和目录、下载文件和数据、保存 Web 方法返回的数据或处理 XML 文档。
• 工作流任务：它与其他进程通信以运行包或程序、在包之间发送和接收消息、发送电子邮件消息、读取 Windows Management Instrumentation (WMI) 数据或监视 WMI 事件。
• SQL Server 任务：它访问、复制、插入、删除或修改 SQL Server 对象和数据。
• Analysis Services 任务：它创建、修改、删除或处理 Analysis Services 对象。
• 脚本任务：通过自定义脚本扩展包功能。
• 维护任务：执行管理功能，例如备份和收缩 SQL Server 数据库、重建和重新组织索引以及运行 SQL Server Agent 作业。

约束条件

约束条件将包中的容器和任务连接成有序的控制流。您可以控制任务和容器的顺序执行，并指定确定任务和容器是否运行的条件。

数据流

它通过采用管道范式来处理其处理职责，将数据逐条记录地从其源传输到目标，并在传输过程中通过应用转换进行修改。（此规则存在例外，因为某些转换，如 Sort 或 Aggregate，需要在将整个数据集交给下游对应项之前能够查看整个数据集）。用于创建数据流的项可分为三部分。

数据流的元素包括

数据流的元素分为三部分

1. 数据流源：这些元素用于从不同类型的数据源读取数据，如（SQL Server、Excel 工作表等）。
2. 数据流转换：这些元素用于对数据进行处理，如（清理、添加新列等）。
3. 数据流目标：这些元素用于将处理过的数据保存到期望的目标。（SQL Server、Excel 工作表等）。

数据流源

以下列出了可以与各种类型数据源通信的不同项

• DataReader 源：DataReader 源使用 ADO.NET 连接管理器从 DataReader 读取数据并将其通道化到数据流。
• Excel 源：Excel 源连接到 Excel 文件，并根据许多可配置设置选择内容，为数据流提供数据。Excel 源使用 Excel connectionmanager 连接到 Excel 文件。
• 平面文件源：其格式包括 CSV 和固定宽度列 — 仍然很受欢迎。由于多种原因，具体情况可能决定使用 CSV 文件而不是其他格式，这就是为什么平面文件源仍然是流行的数据流数据源。
• OLE DB 源：当通过 OLE DB 提供程序执行数据访问时，使用 OLE DB 源。它是一种相当简单的数据源类型，每个人都熟悉 OLE DB 连接。
• 原始文件源：原始文件源用于导入存储在 SQL Server 原始文件格式中的数据。它是一种快速导入数据的有用方法，也许是前一个包以原始格式输出的数据。
• XML 源：XML 源需要一个 XML Schema Definition (XSD) 文件，这实际上是该组件最重要的部分，因为它描述了 SSIS 应如何处理 XML 文档。

数据流转换

此类别中的项用于执行各种操作以将数据格式化为所需的格式。

• 聚合：聚合转换组件基本上将聚合函数封装到数据流中，例如Count、Count distinct、Sum、Average、Minimum、Maximum、Group By（相对于一个或多个列）。
• 审计：审计转换将系统变量暴露给数据流，这些变量可在流中使用。这是通过向数据流输出添加列来实现的。当您将所需的系统变量映射到输出列时，系统变量将引入到流中并可以使用。
• 字符映射：它在输入列上执行string操作，如Lowercase、Uppercase等。
• 条件拆分：条件拆分任务根据条件拆分数据流。根据评估表达式的结果，数据将根据开发者的指定进行路由。
• 复制列：复制列任务复制输入列集合中包含的列，并将其追加到输出列集合。
• 数据转换：它将数据从一种类型转换为另一种类型。就像类型转换一样。
• 数据挖掘查询：SQL Server 2005 中的数据挖掘实现主要是为了发现数据中事实正确的预测趋势。这在 SSAS 中针对提供的某个数据挖掘算法进行配置。DMX 查询从基于相同挖掘结构的一个或多个此类模型请求预测结果集。可能需要检索使用不同可用算法计算的相同数据的预测信息。
• 派生列：基于任务执行的工作，将一个或多个新列附加到输出列集合，或者派生函数的结果替换现有列值。
• 导出列：它用于从输入流中提取数据并将其写入文件。有一个注意事项：要导出的列的数据类型必须是DT_TEXT、DT_NTEXT或DT_IMAGE。
• 模糊分组：模糊分组用于数据清理。通过设置和调整任务属性，您可以获得出色的结果，因为该任务可以解释输入数据并就其唯一性做出“智能”决策。
• 模糊查找：它使用引用（或查找）表来查找合适的匹配项。引用表需要可用并可选择为 SQL Server 2005 表。它使用可配置的模糊匹配算法来做出智能匹配。
• 导入列：它用于从任何文件或源导入数据。
• 查找：查找任务利用引用数据以及输入列与引用数据中的列之间的连接，为源值提供逐行查找。此引用数据可以是表、视图或数据集。
• 合并：合并任务将两个独立的排序数据集合并为一个数据集，该数据集表示为单个输出。
• 合并联接：合并联接转换使用联接来生成输出。但是，该任务编辑器允许您以图形方式进行设置，而不是要求您输入包含join的查询（例如SELECT x.columna, y.columnb FROM tablea x INNER JOIN tableb y ON x.joincolumna = y.joincolumnb）。
• 多播：多播转换接收一个输入并将其复制成任意数量的副本，这些副本被定向为不同的输出。输入可以被复制任意多次。
• OLE DB 命令：OLE DB 命令转换对输入流中的每一行执行 SQL 语句。在很多方面，它就像一个高性能的光标。
• 百分比抽样：百分比抽样转换根据数据样本生成并输出一个dataset到数据流。样本完全随机，以代表可用数据的有效横截面。
• 透视：透视转换基本上封装了 SQL 中透视查询的功能。透视查询通过围绕一个中心点（一个值）“旋转”数据来使规范化的数据集非规范化。
• 行计数：行计数任务计算通过组件的行数。它使用指定的变量来存储最终计数。它是一个非常轻量级的组件，因为不涉及任何处理，因为计数只是输入行集合的一个属性。
• 行抽样：行抽样任务，与我之前讨论的百分比抽样转换类似，用于从数据流中创建（伪）随机数据集。此转换对于执行通常针对表中保存的完整数据集执行的操作非常有用。然而，在高容量 OLTP 数据库中，有时这是不可能的。执行针对代表性数据集子集的任务的能力是一种合适且有价值的替代方案。
• 排序：此转换比平均 SQL 语句中的等效ORDER BY子句更进一步，因为它还可以删除重复值。
• 脚本组件：脚本组件用于在转换中编写自定义脚本代码。它不仅可以用作转换，还可以用作源或目标组件。
• 缓慢变化维度：缓慢变化维度任务用于维护数据仓库中的维度表。它是一个高度特定的任务，充当 OLTP 数据库和相关的 OLAP 数据库之间的通道。
• 术语提取：此转换从输入列中提取术语，然后将其作为输出列传递到数据流中。源列数据类型必须是DT_STR或DT_WSTR。
• 术语查找：此任务包装了术语提取转换的功能，并使用提取的值与引用表进行比较，就像查找转换一样。
• Union All：就像 SQL 中的Union All语句一样，Union All任务将任意数量的输入合并到一个输出中。与合并任务不同，此转换中不进行排序。输出的列和数据类型在第一个输入连接到任务时创建。
• 逆透视：此任务基本上封装了 SQL 中逆透视查询的功能。逆透视查询通过将数据“旋转”回一个中心点（一个值）来增加非规范化或去规范化数据集的规范化。

数据流目标

最后，处理过的数据将借助这些项保存在目标中。

• 数据挖掘模型训练：它使用上游数据流中的排序数据来训练数据挖掘模型。接收到的数据通过 SSAS 数据挖掘算法传递给相关模型。
• DataReader 目标：可以通过连接到 DataReader 目标来使用从 .NET 程序集执行的 SSIS 包的结果。
• 维度处理：维度处理是另一个与 SSAS 相关的目标组件。它用于加载和处理 SSAS 维度。
• Excel 目标：Excel 目标提供了有关目标 Excel 文件应如何访问的多个选项。（Table或View、TableName或ViewName变量以及 SQL 命令）。
• 平面文件目标：平面文件目标组件将数据写入文本文件，格式为标准平面文件格式之一：分隔符、固定宽度、带行分隔符的固定宽度。
• OLE DB 目标：OLE DB 目标组件将数据插入任何符合 OLE DB 的数据源。
• 分区处理：分区处理目标类型加载并处理 SSAS 分区。在许多方面，它几乎与维度处理目标完全相同 — 至少在配置方面。您选择或创建 SSAS 连接管理器，选择要处理的分区，然后将输入列映射到所选分区中的列。
• 原始文件目标：原始文件目标侧重于原始速度。它是一种完全本地化的格式，导出和导入速度比任何其他连接类型都快，部分原因是数据无需通过连接管理器。
• 记录集目标：记录集目标创建 ActiveX Data Objects (ADO) Recordset 的实例，并使用指定输入列中的数据填充它。
• SQL Server 目标：SQL Server 目标提供了到 SQL Server 数据库的连接。输入数据的选定列将批量插入到指定的表或视图中。换句话说，此目标用于填充 SQL Server 数据库中的表。
• SQL Server Mobile 目标：SQL Server Mobile 目标组件用于连接到 SQL Server Mobile（或 SQL Server Compact Edition）数据库并向其写入数据。

结论

如前所述，我们在此更加强调了 SSIS 的功能和概述。在接下来的系列文章中，我们将更详细地讨论每个控件，并通过示例进行探索。

历史

• 2011年7月1日：初始版本