SQL Server Integration Services (SSIS)：完全指南

企业数据通常分布在多个数据库、应用程序和文件系统中。高效地移动和转换这些数据对于报表、分析和日常运营至关重要。SQL Server Integration Services (SSIS) 是微软的 ETL 平台，用于在 SQL Server 生态系统中构建自动化的数据集成和工作流解决方案。

本指南将介绍 SSIS 的工作原理、如何创建和运行 SSIS 包、常见的故障排查技术，以及何时实时数据库复制可能是企业数据集成的更好选择。

什么是 SQL Server Integration Services (SSIS)？

SQL Server Integration Services (SSIS) 是微软用于构建数据集成和工作流解决方案的平台。它主要用于 ETL（抽取、转换、加载）流程，使组织能够从多个来源抽取数据，进行转换，然后加载到目标系统中。

SSIS 与 SQL Server 的其他组件协同工作，以实现数据移动自动化并支持数据分析。

• SQL Server 数据库引擎： 从 SQL Server 数据库抽取数据并将数据加载到 SQL Server 数据库中。
• SQL Server Management Studio (SSMS)： 帮助管理和监控已部署的 SSIS 包以及 SQL Server 代理作业。
• SQL Server Analysis Services (SSAS)： 在 SSIS 加载新数据后处理数据模型。
• SQL Server Reporting Services (SSRS)： 使用 SSIS 准备的数据生成报表。

在 SQL Server 2005 之前，微软使用 Data Transformation Services (DTS) 来处理基本的 ETL 任务。随着企业工作负载的增长，DTS 已无法再提供组织所需的可扩展性和工作流能力。

SSIS 取代了 DTS，其重新设计的基于内存的处理引擎提供了更快的 ETL 性能、高级转换功能和更可靠的工作流自动化。

SQL Server Integration Services 的工作原理

SQL Server Integration Services (SSIS) 遵循 ETL（抽取、转换、加载）流程在系统之间移动数据。它从一个或多个源抽取数据，进行转换以满足业务需求，然后加载到目标中，例如 SQL Server、数据仓库或其他应用程序。

SSIS 的核心组件

每个 SSIS 包都依赖四个核心组件来执行 ETL 工作流：

• 控制流： 定义工作流并确定任务的执行顺序。
• 数据流： 在源和目标之间移动数据，同时应用转换。
• 连接管理器： 存储数据源、目标和外部服务的连接信息。
• 事件处理程序： 响应包事件，例如记录错误或发送通知。

实际应用示例：将 Excel 数据导入 SQL Server

一个常见的用例是将销售数据从 Excel 电子表格导入 SQL Server。SSIS 读取 Excel 文件，将数据转换为所需格式，然后加载到目标表中。这种自动化工作流消除了重复的手动导入操作，有助于保持数据的一致性和准确性。

如何创建和运行 SSIS 包

SSIS 包是 SQL Server Integration Services 中的核心部署单元。它包含执行 ETL 流程所需的任务、连接和工作流逻辑。包在 Visual Studio 中创建，可以在本地测试，然后部署到 SQL Server 上并按计划执行。

SSIS 包的关键组件

一个 SSIS 包由几个关键组件构成：

• 任务： 独立的工作单元，例如执行 SQL 语句、复制文件或运行数据流。
• 容器： 用于组织相关任务或使用循环重复操作的结构，例如 Foreach 循环容器。
• 变量： 存储在内存中且可在包执行期间改变的值，例如文件路径或日期。
• 参数： 用于配置包而无需修改其设计的外部值。
• 优先约束： 根据成功、失败或完成状态来控制任务执行顺序的规则。

SSIS 设置分步指南

按照以下步骤创建、测试和部署你的第一个 SSIS 包。

步骤 1：安装开发工具

SSIS 包是在 Visual Studio 中使用 SQL Server Integration Services Projects 扩展进行开发的。

1. 安装 Visual Studio（Community、Professional 或 Enterprise 版）。
2. 在 Visual Studio 中，打开 扩展 > 管理扩展，然后安装 SQL Server Integration Services Projects。
3. 重启 Visual Studio，如有提示，请安装所需组件。

步骤 2：创建新项目

打开 Visual Studio，选择 创建新项目，然后选择 Integration Services Project。为项目命名并打开默认的 Package.dtsx 文件。

步骤 3：构建包

在 控制流 设计器中，将 数据流任务 拖放到画布上。打开 数据流 选项卡，然后添加并配置数据源、所需的任何转换以及目标。

步骤 4：运行并测试包

按 F5 或选择 开始 在本地执行包。在执行期间，SSIS 会显示每个组件的状态：黄色表示正在运行，绿色表示成功，红色表示失败。

步骤 5：部署包

当包准备就绪后，右键单击项目并选择 部署。Integration Services 部署向导将引导你完成将项目部署到 SQL Server 实例上的 SSIS 目录 (SSISDB) 的过程。

步骤 6：计划包执行

部署完成后，你可以使用 SQL Server 代理 自动执行包。在 SQL Server Management Studio (SSMS) 中，创建一个新的 SQL Server 代理作业，并配置作业步骤以按计划运行已部署的 SSIS 包。

SSIS 与其他微软数据工具的比较

微软提供了多种用于移动、转换和管理数据的工具。选择正确的工具取决于你的工作负载、部署环境和自动化需求。

下表比较了 SQL Server Integration Services (SSIS) 与其他常用的微软数据工具。虽然 SSMS 并不是数据集成工具，但由于它和 SSIS 都是 SQL Server 生态系统的一部分，因此经常被拿来与 SSIS 比较。

如何选择合适的工具

导入/导出向导 适用于简单的、一次性的数据传输，但其转换和自动化能力有限。SQL Server Management Studio (SSMS) 是为数据库管理和脚本编写而设计的，而非用于构建 ETL 工作流。

对于在本地运行 SQL Server 工作负载的组织来说，SSIS 仍然是自动化复杂 ETL 流程和集成多源数据的可靠选择。如果你的组织正在构建云原生数据管道，或在 Azure 或 Microsoft Fabric 中处理大规模分析工作负载，那么 Azure Data Factory 或 Fabric Data Factory 通常是更好的选择。

常见的 SSIS 错误与故障排查

即使是设计良好的 SSIS 包也可能因为验证问题、连接问题、数据类型不匹配或权限设置而失败。了解这些常见错误可以帮助你更高效地排查包的问题。

1. 包验证错误

在执行之前，SSIS 会验证连接管理器、源文件和目标对象。如果在验证期间所需的文件或表不存在，包可能会在启动之前就失败。

如何修复： 将受影响任务或连接管理器的 DelayValidation 属性设置为 True。这将验证延迟到运行时，从而允许在包执行期间创建的资源在稍后进行验证。

2. 连接失败

连接错误通常在将包部署到其他服务器后发生。常见的消息包括 “Login failed for user” 或 “The provider is not registered on the local machine.”

如何修复： 验证 SQL Server 代理服务帐户是否具有访问数据库和网络位置所需的权限。如果包依赖于 32 位提供程序（如 Excel 或 Access 驱动程序），请在需要时配置执行以使用 32 位运行时。

3. 数据类型和截断错误

SSIS 强制执行严格的数据类型。例如，加载的值长度超过目标列允许的长度将触发截断错误。

[Destination [2]] Error: An error occurred while writing to the database.

The column "CustomerName" was truncated.

如何修复： 使用 数据转换 转换来匹配目标数据类型，或配置 错误输出 将无效行重定向，而不是停止整个包。

4. SQL Server 代理执行失败

在 Visual Studio 中成功运行的包，在被 SQL Server 代理执行时可能失败，因为它是在不同的安全上下文下运行的。

如何修复： 确保 SQL Server 代理服务帐户或 SSIS 代理帐户有权访问所有必需的文件、文件夹和网络资源。

5. 调试技巧

SSIS 包含几个内置工具，可以在开发过程中简化故障排查。

• 数据查看器： 显示组件之间流动的数据，以便在记录到达目标之前检查它们。
• 断点： 在特定事件处暂停包执行，以检查变量并确定错误来源。

当 SSIS 不够用时：实时数据库复制

SSIS 是为计划性的 ETL 工作流而设计的，因此非常适合数据仓库、报表和定期的数据集成。然而，它并不适用于持续的数据库同步。缩短 SSIS 包的执行间隔会增加复杂性，同时更新之间仍会存在延迟。

当需要接近实时的数据复制时，例如用于灾难恢复、运营报表或混合云同步，专门的复制解决方案通常是更好的选择。这正是 i2Stream 能够补充 SSIS 的地方，它通过持续捕获数据库变更并以极低的延迟进行复制。

对于正在评估 i2Stream 与基于 SSIS 的工作流相比之下的团队来说，i2Stream 具有以下几个相关特性：

• 基于日志的无代理捕获： i2Stream 直接读取数据库日志，而非在生产系统上部署代理，因此复制对源数据库没有性能影响。这与 SSIS 通常在抽取期间直接查询源表的方式不同。
• 毫秒级同步，保证事务一致性： 变更以接近实时的速度进行复制，同时保持事务级别的完整性，并支持集成的 DML 和 DDL 同步。这弥补了计划性 SSIS 包无法避免的延迟差距。
• 广泛的跨平台支持： i2Stream 支持 40 多种数据库和大数据环境，包括 Oracle、SQL Server、MySQL 和 PostgreSQL，并能够在异构平台和版本之间进行复制。这使得它非常适合整合分支机构数据库或在零停机的情况下在不同数据库类型之间进行迁移。
• 灵活的复制拓扑： 一对一、一对多、多对一和级联复制模式支持诸如将数据分发到边缘节点或将多个来源集中到单个数据仓库等场景，而单个 SSIS 包在设计上无法大规模处理这些任务。
• 内置数据校验： 自动化的 MD5 校验和比对，配合可视化偏差分析和一键修复，让团队对复制数据与源数据匹配充满信心，无需手动核对。

SSIS 和 i2Stream 解决的是不同的问题。SSIS 处理 SQL Server 生态系统内的计划性 ETL 和数据转换。i2Stream 处理跨平台的持续复制和同步，包括根据你的恢复点目标而定制的 同步和异步复制模型。对于既需要批量转换又需要实时同步的团队来说，这两者通常是并行运行的，而不是相互替代。

英方软件还提供 i2Move，用于一次性的跨平台数据库和系统迁移，以及 i2CDP，用于提供接近零 RPO 的持续数据保护，适用于需求超出持续复制的团队。

结论

SQL Server Integration Services (SSIS) 仍然是一个可靠的平台，用于构建计划性 ETL 工作流、自动化数据集成以及管理 SQL Server 生态系统中复杂的数据转换。理解其架构、包设计、部署流程和常见的故障排查技术，将帮助你构建更高效且更易于维护的 ETL 解决方案。

对于需要持续数据库同步而非计划性批处理的组织来说，像 英方软件 的 i2Stream 这样的专用复制解决方案可以补充 SSIS，提供接近实时的、跨平台的数据复制。在现代企业环境中，SSIS 和 i2Stream 可以共同支持批量 ETL 和持续数据集成。