读书《100 SQL Server Mistakes and How to Avoid Them》第六章 SSIS开发

第六章 SSIS开发

本章涵盖

• SSIS 简介和 SSIS 开发错误
• 丢失坏数据
• 不优化数据加载
• 将 SSIS 用作 T-SQL 编排工具
• 总是从源表中提取所有数据

SQL Server 集成服务，通常称为 SSIS，是随 SQL Server 企业版和标准版一起提供的工具，尽管标准版在功能上有所限制。它是一种提取、转换和加载 (ETL) 工具，允许开发人员在一个拖放式图形用户界面中构建数据移动和转换管道。

注意 本章中的示例将使用 SQL Server 数据工具，可以从 https://mng.bz/RNB0 下载。还需要安装 Integration Services 扩展，可以在“扩展 | 管理扩展”中找到，然后通过在市场中搜索 SQL Server Integration Services Projects 2022 进行安装。

一个 SSIS 包总是由一个控制流组成，该控制流协调包将运行的任务。在控制流中，我们可以创建零个或多个数据流，这些数据流用于在内存缓冲区中导入、导出和转换数据。

控制流上的任务由优先约束连接。这使我们能够设计我们的包以串行运行任务，而不是所有任务并行运行。遵循优先约束的任务总是在约束前的任务之后执行。

约束 SSIS 有三种类型的约束：成功、失败和完成。这些允许我们控制包的流程并创建自定义错误处理逻辑。连接到约束的任务具有以下行为： 与成功约束相关的任务将在前一个任务成功完成后执行。如果前一个任务失败，则不会执行。 与失败约束相关的任务将在前一个任务失败后执行。如果前一个任务成功，则不会执行。 与完成约束相关的任务将在前一个任务执行后运行。无论前一个任务成功还是失败，它都会被执行。 在 SSIS 控制流中，成功约束显示为绿色，失败约束显示为红色，完成约束显示为蓝色。

控制流中的优先级约束不应与数据流中的数据路径混淆。在数据流中，蓝色数据路径表示成功通过前一个组件的行将被传递到下一个组件。红色数据路径表示未通过前一个组件的行将被传递到下一个组件。没有完成数据路径。

当我们从集成服务项目模板创建项目时，我们会看到面前有一个空的控件流，并且左侧窗格中有 SSIS 工具箱。这个工具箱是我们可以将任务拖到控件流上或将组件拖到数据流上的地方。工具箱是上下文敏感的，因此如果我们处于数据流中，我们将看到数据流组件，而不是控件流任务。

每个数据流实际上都是我们控制流中的一个任务，因此我们可以通过将数据流任务拖到控制流上来创建一个数据流。然后，我们可以通过双击任务或切换到设计面板顶部的“数据流”选项卡来查看数据流。

连接管理器用于创建与数据源（如数据库和扁平文件）的连接。开箱即用的连接管理器包括 OLE DB 连接、ADO.NET 连接、文件、扁平文件和分析服务。为了性能，当在数据流中连接到 SQL Server 实例时，OLE DB 通常是最佳选择。ADO.NET 连接管理器通常用于在控制流中连接到 SQL Server 执行 T-SQL 语句任务。

OLE DB 配置 几年前，微软不再推荐使用 OLE DB。随后在 2018 年，它又被重新推荐，并发布了新的 OLE DB 驱动程序。然而，SQL Native Client 11 仍然不再推荐使用，并且不会随 SQL Server 2022 一起提供。因此，要在连接到 SQL Server 的应用程序（包括 SSIS）中使用 SQL Server OLE DB，您需要下载并安装 Microsoft OLE DB Driver 19 for SQL Server，下载地址为 https://mng.bz/AagK。 Microsoft Visual C 可再发行组件是此驱动程序的先决条件，因此需要先安装此组件。此可再发行组件可以在 https://mng.bz/ZVXO 找到。 一旦这些软件包安装完成，正确的 OLE DB 驱动程序将在提供程序下拉菜单中出现。然而，默认情况下，连接管理器会将“使用数据加密”属性设置为“强制”。除非您为 SQL Server 实例配置了证书，否则您需要将其更改为“可选”。可以使用连接管理器对话框中的“数据链接”按钮找到该属性，并导航到“全部”选项卡。

开发人员在开发 SSIS 包时可能会犯各种错误。在本章中，我们将探讨一种可能导致数据丢失的错误。我们将研究不优化数据加载的结果。我们还将查看仅将 SSIS 用作编排工具而未利用数据管道的影响。最后，我们将探讨未对数据提取进行过滤的错误。

为了探讨在使用 SSIS 时常见的一些错误，我们将继续使用 MagicChoc 示例，并使用以下场景。市场部将开始在网上投放广告，并希望跟踪潜在客户看到的展示次数（在线广告横幅的浏览次数）。展示数据将以 CSV 文件的形式接收，由 SSIS 包进行提取，该包计划每天运行一次。该包会将数据放入 Marketing 数据库的暂存模式中的一个表中。然后，它会将数据转换到 marketing 模式中的一个表中。最后，它会汇总数据并插入 reporting 模式中的汇总表，然后清空暂存表。

如果你希望跟随本章的示例，请查看本书代码仓库中的示例 CSV 文件。它的名称是 impressions.csv。你还需要使用下列清单中的脚本创建 Marketing 数据库。

CREATE DATABASE Marketing ; GO   USE Marketing ; GO   CREATE SCHEMA staging ; GO   CREATE SCHEMA marketing ; GO   CREATE SCHEMA reporting ; GO   CREATE TABLE staging.ImpressionsStage ( ImpressionUID        VARCHAR(MAX)    NULL, ReferralURL          VARCHAR(MAX)    NULL, CookieID             VARCHAR(MAX)    NULL, CampaignID           VARCHAR(MAX)    NULL, RenderingID          VARCHAR(MAX)    NULL, CountryCode          VARCHAR(MAX)    NULL, StateID              VARCHAR(MAX)    NULL, BrowserVersion       VARCHAR(MAX)    NULL, OperatingSystemID    VARCHAR(MAX)    NULL, CostPerMille         VARCHAR(MAX)    NULL, EventTime            VARCHAR(MAX)    NULL, BidPrice             VARCHAR(MAX)    NULL ) ;   CREATE TABLE marketing.Impressions ( ImpressionID       BIGINT            NOT NULL    PRIMARY KEY  IDENTITY, ImpressionUID      UNIQUEIDENTIFIER  NOT NULL, ReferralURL        VARCHAR(512)      NOT NULL, CookieID           UNIQUEIDENTIFIER  NOT NULL, CampaignID         BIGINT            NOT NULL, RenderingID        BIGINT            NOT NULL, CountryCode        TINYINT           NULL, StateID            TINYINT           NULL, BrowserVersion     BIGINT            NOT NULL, OperatingSystemID  BIGINT            NOT NULL, BidPrice           MONEY             NOT NULL, CostPerMille       MONEY             NOT NULL, EventTime          DATETIME          NOT NULL ) ;   CREATE TABLE reporting.ImpressionAggregates ( ImpressionAggregateID    BIGINT    NOT NULL    PRIMARY KEY  IDENTITY, CampaignID               BIGINT    NOT NULL, CountryCode              TINYINT   NOT NULL, EventDate                DATE      NOT NULL, AvgBidPrice              MONEY     NOT NULL, AvgCostPerMille          MONEY     NOT NULL ) ;

在 Visual Studio 中，我们将创建一个名为 ImpressionsLoad 的新项目，该项目将使用集成服务项目类型（需要安装）。解决方案应使用相同的名称，并且项目可以在与解决方案相同的目录中创建。

错误22# 丢弃错误数据

我看到 SSIS 开发人员最常犯的一个错误是丢弃他们无法加载的行。通常，由于对 SSIS 工作方式的误解，开发人员甚至不知道自己在这样做。为了探讨这一点，让我们创建一个简单的 SSIS 包，其中包含一个数据流，该数据流将从我们的 impressions.csv 文件（我已将其放置在 c: 根目录下）加载展示数据到我们的 staging.impressions 表中。

首先，将一个数据流任务拖到控制流中，并将其重命名为“加载展示量暂存”。我们还应该创建两个连接管理器：一个用于 CSV 文件，另一个用于 SQL Server 实例。

要为 CSV 文件创建连接管理器，我们应该选择平面文件连接。在对话框的“常规”页面上，我们可以配置连接的基本详细信息，例如文件名称、文件编码和文件类型（分隔、定宽或不规则）。在对话框的“列”页面（图 6.1）上，我们应确保列分隔符配置为逗号。其他可能的分隔符包括制表符、分号和竖线等。高级页面允许我们指定各个列的属性，例如数据类型。预览页面显示文件中的前 100 行。

在对话框的高级页面中，我们应该设置我们的数据类型。SSIS 可以根据前 100 行为我们建议数据类型，但除非我们确定前 100 行能够充分覆盖数据中的可能性，否则我们应该自己选择数据类型。由于我们正加载到一个具有广泛数据类型的暂存表中，因此我们暂时只将数据类型保留为 50 字符长度的字符串。

我们的第二个连接管理器将是一个 OLE DB 连接管理器，它指向托管营销数据库的 SQL Server 实例。在这个连接管理器中，我们首先会从下拉列表中选择我们想要使用的 OLE DB 提供程序。在我们的例子中，我们将选择 Native OLE DBMicrosoft OLE DB Driver 19 for SQL Server。然后我们将指定 SQL Server 实例的名称以及我们将要连接的数据库名称（初始目录）。此外，我们还可以选择如何对实例进行身份验证。如果我们使用 Windows 集成安全性，那么包将以运行该包的身份进行身份验证。如果我们选择 SQL Server 用户名和密码，那么包将使用二级身份验证，也称为 SQL 身份验证进行身份验证。

回到数据流中，我们现在可以创建一个平面文件源，它将使用我们的平面文件连接管理器，以及一个 OLE DB 目标，它将使用我们的 OLE DB 连接管理器。

数据流源的“列”页面允许我们将外部列（来自数据源的列）映射到输出列（即从我们的组件传递到数据流中下一个组件的列）。我们可以通过在上方窗口中取消选择来移除外部列，并且可以更改输出列的名称。对于我们的具体用例，我们将把外部列 ImpressionUID 映射到名为 ImpressionID 的输出列。

在平面文件源的错误输出页面上，我们可以定义如果通过该源的任何行出现失败时组件的行为。我们可以区分一般错误或数据截断，并指定组件的不同行为。现在，我们将保留默认值，这意味着如果某行未能通过源传入数据流，它将导致组件失败。

为了完成简单的数据流，我们还需要一个 OLE DB 目标。在目标中，我们可以选择是将数据加载到命名对象中，还是从变量中获取对象。对于每个选项，我们还可以选择是否查看快速加载选项。这些选项将允许我们优化加载性能；我们将在本章的下一节中详细讨论。在我们的例子中，我们将使用表或视图快速加载数据访问模式，并选择 staging.ImpressionStage 作为目标，如图 6.2 所示。

注意：将快速加载选项保持为默认设置是一个错误。我们应该始终花时间来配置这些选项。这是未优化加载的错误的一部分，我们将在下一节中讨论这一点。

在 OLE DB 目标的映射页面上，任何输入列（来自前一个组件的列）如果名称相同，将会自动映射到目标列（目标表中的列）。然而，ImpressionID 列未被映射，因为名称不同。因此，我们必须从下拉列表中选择它。如果需要，我们也可以修改自动映射。

目标的错误输出页面定义了当一行无法插入时组件的行为。然而，就像源的错误输出一样，我们将保持默认的“组件失败”。我们目标表中的列非常多，因此可恢复行失败的可能性很小。

现在让我们通过点击工具栏中的开始按钮以调试模式执行该包，并看看会发生什么。结果如图6.3所示。您会注意到我们已成功将954,912行加载到暂存表中，但在Impressions CSV源处发生了错误。这由红色叉号表示。

如果我们切换到进度选项卡，我们可以看到在第 999,969 行出现了数据截断错误，如图 6.4 所示。

真烦人！文件中只有100万行！我们可不想因为一行错误而丢掉整个文件，对吧？所以我们回到平面文件源编辑器的错误输出页面，改变组件的行为，这样如果遇到任何错误，我们将忽略有错误的行并继续加载数据。如图6.5所示。

提示 在修改包之前，点击停止调试按钮。

提示 在再次运行该包之前，截断暂存数据以避免重复加载数据。我们可以使用 TRUNCATE TABLE Staging.ImpressionsStage 命令来完成此操作。

现在，如果我们再次运行这个包，我们会看到数据流已经成功。每个组件都会显示一个绿色勾号来表示成功。这听起来不错，对吧？其实不然。实际上，这是一个错误。它可能解决了短期问题，但如果下一个文件有五行错误呢？或者50行呢？如果有5000行错误呢？cookie ID 是一个重要的列，我们需要它，所以不应该丢弃这些错误数据。但我们也不希望每次包运行都失败。那么，我们应该怎么做呢？

答案是将错误的行重定向到不同的目的地，以便应用程序支持团队可以检查它们，决定是否可以安全地丢弃，或者我们是否可以修复这些数据。甚至可能需要应用程序所有者联系供应商并请求一个新的数据提取。来自不可信来源的数据通常是脏的，我们应该在代码中考虑到这一点。

为了解决这个问题，让我们先创建一个新表，该表将保存任何执行任务失败的行。我们可以使用下面列表中的脚本来实现这一点。

CREATE TABLE staging.ImpressionLoadFailures ( FlatFileSourceErrorOutputColumn    VARCHAR(MAX)    NOT NULL, ErrorCode                          VARCHAR(MAX)    NOT NULL, ErrorColumn                        VARCHAR(MAX)    NOT NULL ) ;

接下来，我们将创建一个新的 OLE DB 目标，并将其命名为 Failed Rows。从数据源将红色连接器拉到新的数据流目标。按照我们当前的配置，这将立即导致显示“配置错误输出”对话框。此对话框与数据源编辑器的“错误输出”页面相同。我们应配置此页面以重定向包含错误和截断的行。

现在让我们配置新的目标。在连接管理器页面，我们将把新的 ImpressionLoadFailures 表指定为目标表。在映射页面，我们需要手动映射平面文件源错误输出列，因为我们的表列名中没有空格，所以自动映射将无法工作。我们的数据流现在将如图 6.6 所示。

如果我们现在再次运行该包，失败的行将被插入到我们的 ImpressionLoadFailures 表中。我们可以检查该行：

91C27789-2805-41EB-8E06-A4F5AD147817;jouktjkatujr.com, 7D522130-5A00-4C4B-83D5-EA0A7E1777CF, 54879,4433265,220,NULL,128,142,3.3635,1.2668,02/07/2023 07:56

我们知道我们期望文件是用逗号分隔的，但我们可以看到在第一个全局唯一标识符（GUID）之后，是分号而不是逗号。这会导致该行失败，因为在 Cookie ID 列上会出现截断错误。这是因为我们使用了建议的数据类型——50字符的字符串，而该列的长度（直到我们遇到第一个逗号）是53个字符。

提示 在某些情况下，我们还希望向数据源目标添加失败约束。然而，在我们的场景中，我们已经配置了暂存表，使所有列都是 VARCHAR(MAX)，这已经是我们能够设置的最宽泛的配置。因此，没有必要设置失败源，因为我们无法使其列更宽泛。

我们应该始终避免丢弃错误的数据。忽略行的做法可能方便，但如果这些数据足够重要以至于需要加载，那么它们也应该足够重要以至于需要保留，即使在之后的复查中发现这些数据无法修复。如果某一列不需要，那么我们应该避免加载它。这会增加包失败的可能性，并且会使行比实际需要的更大，这也可能带来性能上的损失。本章后面会讨论这一点。

错误23# 未优化数据加载

在上一节中，在构建我们的数据流时，你可能注意到我们犯了一个额外的错误——即没有优化我们的数据加载。如果没有这种优化，我们的数据包将永远无法达到最佳性能。

为了优化加载，我们应该在数据流上配置属性，这些属性控制数据从 CSV 文件读取时将被加载的数据缓冲区的大小。然后，在 OLE DB 目标上还有快速加载选项，可以用来优化批量加载到 SQL Server 的过程。

现实世界的package优化 有很多场合下，优化负载是绝对必要的。我多次看到的一个例子是，当季度和年度财务交易数据从财务系统加载到报表工具中时，必须在特定的时间窗口内完成。然而，另一个很好的例子是，当我处理与本章讨论的相同数据领域时。 在本章中，我们只有一个平面文件，其中只有 100 万行。然而，我记得曾经为一个大型广告集团工作。我们必须每晚加载展示数据、点击数据和事件数据，然后进行复杂的点击路径分析。来自多个 cookie 提供商的文件不止一个。有数亿的展示、数千万次点击，以及每晚必须处理的数百万个事件。 由于我们的样本文件的大小，我们将寻求节省数秒。然而，在实际情况中，优化数据加载节省了许多分钟。这一点至关重要，因为文件通常在午夜之后才交付，而它们必须在工作日开始前准备好。ETL 过程需要花费数小时才能完成，如果没有加载优化，它们将超出时间窗口，对业务产生每日影响。

控制数据流大小的属性是 DefaultBufferMaxRows，它控制缓冲区内的最大行数，以及 DefaultBufferSize，它控制数据缓冲区的最大大小。默认的最大行数为 10,000，默认的最大大小为 1MB。缓冲区大小将由先达到的限制控制。

这可能会让人感到困惑，所以我建议将 AutoAdjustBufferSize 属性设置为 True。这将导致不必担心 BufferSize 属性，因为它会自动配置以匹配我们在缓冲区中选择的最大行数。

OLE DB 目标中的快速加载选项允许我们指定是否应在列上禁用标识规范，是否应保留 NULL 值，是否应禁用检查约束，以及是否应使用表锁。它还允许我们指定每批次的最大行数和插入的最大提交大小。

因为我们正在将数据加载到一个暂存表中，该表是一个没有键、约束或非空约束的扁平堆，所以在我们的场景中可以忽略这些设置。然而，如果我们确实有这些约束，那么启用它们会带来性能上的损失。我们将确保选中表锁选项，因为这将避免争用并减少锁升级的开销。

最大批量大小和提交大小是需要配置的重要值。默认情况下，数据将以单个事务加载到表中。这可能会导致性能问题，因为会过度使用 TempDB 并填满事务日志。我们怀疑从平面文件读取数据的速度会比向临时表插入数据慢，因此我们将根据数据流源中的行数来保持最大批量和提交大小一致。

不幸的是，关于每个缓冲区或每个批次中应该包含多少行，并没有一个固定的魔法数字。最佳值将非常依赖于您独特的环境、资源配置、工作量特征和数据特征。找到最佳值的方法是使用多种大小选项测试数据流。

提示 当进行此操作时，我通常从一个小值开始，然后逐步增加规模。这通常会带来渐进的性能提升，但提升会逐渐减缓。当执行时间开始增加时，我就知道已经达到了最佳规模。

表 6.1 包含了我对数据流进行的各种测试结果。第一列详细说明了缓冲区中的最大行数，这与每批次的最大行数一致。第二列显示了包的执行时间，以秒为单位。这些执行时间可以在停止执行前从“进度”选项卡中获取，也可以在执行停止后从“执行结果”选项卡中获取。

提示 如果你想在自己的环境中运行测试，记得每次都清空临时表以保持测试的公平性。还要记住，你的表现会有所不同，这取决于你的计算机规格和可能正在运行的其他进程。

在我的环境中，很明显可以看出50,000行是缓冲区的最佳大小。不过，我们也应该验证一个假设，即平面文件源将成为限制因素。因此，让我们进行更多的测试。这一次，我们将把每次测试的 MaximumRowsPerBuffer 保持在50,000，但调整批处理大小。这些测试的结果可以在表6.2中找到。再次提醒，这些结果将针对您特定的环境，因此实际情况可能会有所不同。

如预期，结果显示读取平面文件是限制因素，我们无法通过取消批量大小与缓冲区大小的对齐来获得性能提升。

我们应始终优化我们的加载性能。性能不佳可能导致数据包超出其ETL窗口。这可能会影响业务或维护流程，例如备份。

错误24# 使用 SSIS 作为 T-SQL 编排工具

我经常看到的新手 SSIS 开发人员所犯的一个常见错误是避免使用数据流，而仅在控制流中使用执行 T-SQL 任务在表之间或进行转换加载数据。

这种方法导致开发人员无法利用 SSIS 的强大功能。这意味着我们只是运行标准的 T-SQL。ETL 将无法与我们的数据源实现松耦合，后续的 ETL 开发人员将无法获得逻辑的图形表示，我们也无法利用 SSIS 的错误处理和日志记录功能。错误行将导致整个查询失败，而无法单独重定向错误行。此外，我们将失去在管道中探索数据位置的能力，从而使调试性能问题变得更加困难。

SSIS 性能 值得注意的是，如果源表和目标表都在同一服务器上，那么使用原生 T-SQL 可能比使用数据流管道进行数据转换的性能更高，因为它在连接和合并数据等操作上表现更好。因此，对于一些涉及单个数据库服务器的大规模负载，原生 T-SQL 可能是正确的方法，尤其是在 ETL 窗口较短的情况下。我们应当为工作选择合适的工具。 然而，可以说，在这种情况下，运行 SSIS 的开销可能几乎没有好处，我们或许更适合运行一个 SQL Server Agent 任务，并使用任务步骤来协调工作流。在服务器之间加载和转换数据时，SSIS 通常能够比链接服务器有更好的性能。 同样值得权衡性能与功能。如果我们对 ETL 窗口的长度没有任何限制，我们可能会认为 SSIS 的错误处理、日志记录和调试工具比速度更重要。

为了探索将 SSIS 用作纯编排工具的效果，让我们扩展我们的包，将数据从暂存表转换到核心营销表，然后再到报表表。最后，我们的包应该清空暂存表。接下来的部分将讨论如何使用控制流作为编排工具来创建 Execute T-SQL Statement 任务。

创建一个执行 T-SQL 语句的编排

我们在增强此包的第一步将是创建一个新的 ADO 连接管理器。此连接管理器将被我们的 Execute T-SQL Statement 任务使用，以连接到我们的 SQL Server 实例。

一旦我们有了连接管理器，我们现在可以创建将执行我们的 SQL 语句的任务。我们应将第一个新的“执行 T-SQL 语句”任务配置为执行列表 6.3 中的查询。此查询执行 INSERT 语句，将 SQL Server 无法执行隐式转换的数据进行转换。开头的 SET DATEFORMAT 语句是必需的，以便 SQL Server 能识别 EventTime 列中的日期。没有它，任务将因数据类型溢出错误而失败。

SET DATEFORMAT DMY INSERT INTO marketing.Impressions ( ImpressionUID, ReferralURL, CookieID, CampaignID, RenderingID, CountryCode, StateID, BrowserVersion, OperatingSystemID, BidPrice, CostPerMille, EventTime ) SELECT ImpressionUID , ReferralURL , CookieID , CampaignID , RenderingID , CASE WHEN CountryCode = ‘NULL’ THEN NULL ELSE CountryCode END CountryCode , CASE WHEN StateID = ‘NULL’ THEN NULL ELSE StateID END StateID , BrowserVersion , OperatingSystemID , CAST(BidPrice AS MONEY) , CAST(CostPerMille AS MONEY) , EventTime FROM staging.ImpressionsStage ;

我们将创建的下一个执行 T-SQL 语句任务称为 Merge Aggregates，用于填充 reporting.ImpressionAggregates 表。对于此任务，我们应使用清单 6.4 中的查询。该查询使用 MERGE 语句按 CampaignID、CountryCode 和 EventDate 汇总数据后再加载汇总表。如果特定的活动、国家和日期已经存在行，则会更新平均出价和平均 CPM。否则，将插入新行。

MERGE INTO reporting.ImpressionAggregates AS Target USING ( SELECT CampaignID , CountryCode , AVG(BidPrice) AS AvgBidPrice , AVG(CostPerMille) AS AvgCostPerMille , CAST(EventTime as DATE) AS EventDate FROM marketing.Impressions GROUP BY CampaignID , CountryCode , CAST(EventTime as DATE) ) AS source ON ( Source.CampaignID = Target.CampaignID AND Source.CountryCode = Target.CountryCode AND Source.EventDate = Target.EventDate ) WHEN MATCHED THEN UPDATE SET AvgBidPrice = Source.AvgBidPrice , AvgCostPerMille = Source.AvgCostPerMille WHEN NOT MATCHED THEN INSERT ( CampaignID, CountryCode, EventDate, AvgBidPrice, AvgCostPerMille ) VALUES ( Source.CampaignID, Source.CountryCode, Source.EventDate, Source.AvgBidPrice, Source.AvgCostPerMille ) ;

我们的最后一个任务将在加载结束时使用列出 6.5 中的语句清空暂存表。清空表是执行 T-SQL 语句任务的一种完全可接受的用法。此任务不能作为数据流的一部分执行。即使在优化包以使用数据流替换以前的执行 T-SQL 语句任务时，我们仍然会保留此任务。

TRUNCATE TABLE staging.ImpressionsStage ;

我们所有的任务都应该由成功约束连接在一起。这将强制任务按顺序运行。这也意味着，如果一个任务失败，后续任务将不会运行。图6.7展示了此时我们的控制流程将如何呈现。

该包现在已完成，但对于加载数据而言并不理想，因为在这种情况下，数据来自不受信任的来源，并且可能是脏的。假设我们已经将一行脏数据加载到我们的暂存数据中。由于我们的暂存表中列很多，我们设法将数据加载到这一步，但当我们尝试加载 marketing.Impressions 表时，该行因数据类型转换失败而失败。在这种情况下，整个任务都会失败。追踪失败原因也将很棘手，因为 SQL Server 不会报告具体失败的行。

那么我们如何才能创建一个更好的包？答案是使用数据流任务。为此，我们先删除“加载营销展示”和“合并聚合”任务。接下来的部分将讨论如何用数据流来替代它们。

将执行 T-SQL 语句任务转换为数据流

加载营销展示数据流将类似于图6.8中所示的流程。您会看到有一个源，它从暂存表中读取数据。然后有一个派生列转换，将列转换为正确的数据类型，然后数据流目标将行插入到marketing.Impressions表中。

源和目标是直接的组件。真正的魔法发生在导出列转换中。在图6.9中，你会注意到我们使用了SSIS表达式，这是列、函数、运算符和字面值的组合，用于创建包含转换值的新输出列。导出列名称字段是自由文本，允许我们为输出列指定名称。导出列字段是下拉菜单，允许我们选择希望替换的列名或将输出作为新列添加。表达式字段可以完全自由构建。或者，可以从对话框左上角拖入列、变量和参数名称。对话框右上角区域允许我们将函数拖入表达式字段。数据类型字段会根据表达式自动填充。右侧还有其他字段，这些字段也会基于输出数据类型自动填充。这些字段包括长度、精度、刻度和代码页。如果表达式中有错误，它将变为红色，并且将鼠标悬停在表达式上会显示解析错误的详细信息。

前两个表达式是在转换 GUID 值。为了让 SSIS 将这些值识别为 GUID，它们必须用大括号括起来。因此，我们显式地将输入列转换为 36 个字符的 Unicode 字符串，并在前后添加大括号。然后，我们将整个值转换为 DT_GUID 数据类型。

将 EventTime 列进行转换的表达式首先会将输入显式转换为 26 个字符的字符串，并将 / 字符替换为 – 字符。这是因为 SSIS 无法识别暂存表中使用的数据格式。最后，我们将结果转换为 DB_TIMESTAMP 数据类型。

将 URL 转换的表达式只是对字符串执行显式转换，使用 1252 代码页。该代码页等同于 Windows 1252 代码页。

将 StateID 列转换的表达式可以说是这些表达式中最有趣的一个。我们使用这个表达式不仅是为了更改数据类型，还为了将字符串值 “NULL” 替换为 NULL 值。对于拥有 .NET 背景的你们来说，这个语法可能有些熟悉，但为了理解这个表达式，让我们将其分解为几个部分。第一部分位于冒号右侧，将 StateID 作为输入列传入，在此之前将其显式地转换为 50 字符的字符串。在冒号左侧，我们检查 StateID 中的值（同样需要显式转换）是否等于 “NULL”。如果是，则将此值替换为 NULL 值。这里还涉及另一次转换，因为在 SSIS 中，每种数据类型都有不同的 NULL 值类型。例如，NULL(DT_WSTR,50) 与 NULL(DT_I4) 是不同的类型。最后，在括号外、最左侧，我们将最终值转换为 DT_UI1，这是一种单字节无符号整数。

此转换中使用的表达式在以下列表中有详细说明。

(DT_GUID)(“{” + (DT_WSTR,36)(ImpressionUID) + “}”)          ① (DT_GUID)(“{” + (DT_WSTR,36)(CookieID) + “}”)               ② (DT_DBTIMESTAMP)(REPLACE((DT_WSTR,26)EventTime,”/”,”-“))    ③ (DT_TEXT,1252)ReferralURL                                   ④ (DT_UI1)((DT_WSTR,50)StateID == “NULL” ? NULL(DT_WSTR,50) : ⑤ (DT_WSTR,50)StateID)                                        ⑤   ① ConvertedImpressionUID ② ConvertedCookieUID ③ ConvertedDate ④ ConvertedURL ⑤ ConvertedStateID

与相应的执行 T-SQL 语句任务相比，这种数据流的最大优势是能够处理错误。如本章前面讨论的，我们可以增强此数据流以包含错误路径，从而将失败的行重定向到数据错误表。这将使应用支持团队能够处理那些无法转换为更严格数据类型的错误行，作为日常业务操作的一部分。使用执行 T-SQL 语句任务时，整个插入操作都会失败，需要进行调试并修复数据，才能使加载的数据可供业务使用。

合并聚合数据流将类似于图6.10。该数据流以数据流源开始，从 marketing.Impressions 表中读取展示数据。然后是派生列转换，将日期和时间数据汇总到四舍五入的日期。聚合转换随后计算平均出价和 CPM。最后，是一个 upsert 目标，如果键组合尚不存在，则将行插入 reporting.ImpressionAggregates 表；如果键组合已经存在，则更新聚合值。

像这样的转换数据流通常比这个示例复杂得多。对于复杂的转换，数据流相对于执行 T-SQL 语句任务具有许多优势。不仅可以单独重定向失败的行，而且我们还可以直观地查看我们的逻辑。这有助于让其他开发人员理解业务逻辑。我们还可以实时观察数据流的执行情况，了解错误发生的地点或性能缓慢的原因。这可以辅助开发过程。

读取营销展示源是从基础表的简单读取。将 Rollup DateTime 转换为日期的转换是派生列转换，它使用 SSIS 表达式 (DT_DBTIMESTAMP)(DT_DBDATE)EventTime 生成一个新的输出列。该表达式首先将值转换为 DT_DBDATE，从而去除时间部分。为了便于数据类型匹配，它随后立即将值转换回 DT_DBTIMESTAMP，从而重新添加时间部分，但由于时间信息现在已丢失，时间将被设置为 00:00:00.000。

汇总展示成本转换使用汇总转换来计算按 CampaignID、CountryCode 和 EventTime 列分组的 BidPrice 和 CostPerMille 的平均值。图 6.11 显示了汇总转换编辑器的汇总页面。您会注意到，在对话框的上半部分，我们已选择了适合该转换的输入列，而在下半部分，我们已指定要应用的适当汇总。对于度量值，我们选择了平均值，并且我们为关键列选择了按组汇总。这将为每个独特的 CampaignID、CountryCode 和 EventTime 组合生成不同的平均成本数据。

提示：其他可用的聚合函数有计数、去重计数、求和、最小值和最大值。

阻塞变换 值得注意的是，SSIS 转换可以是阻塞的或非阻塞的。非阻塞转换，例如派生列转换，会在处理完批次后立即输出批次中的行。然而，其他转换在所有行处理完之前不会传递任何输出。这些称为阻塞转换，因为下游组件必须等到阻塞转换完全处理完毕才能开始处理。聚合转换就是一种阻塞转换。这是因为在接收所有行之前，它无法确认聚合值。否则，它可能会处理并传出错误的聚合结果，因为如果收到具有相同分组键的更多行，聚合值可能会发生变化。

Upsert 目标不是一个开箱即用的 SSIS 组件。虽然有多种方法可以使用原生 SSIS 组件来实现这一结果，但这些方法被认为是不佳的实践，因为它们通常表现出非常差的性能。有各种可用的 upsert 目标，包括 COZYROC 和 SentryOne 提供的，但在本章中，我们将使用 ZappySys 的目标，它是其 SSIS PowerPack 产品的一部分。可通过 https://mng.bz/2gVd 购买。

在 Upsert 目标对话框的设置页面中，我们将选择要执行的操作。在我们的场景中，这是一个 upsert，但该组件也支持同步（upsert 删除）、批量更新和批量删除。然后我们将选择要使用的数据源以及将作为目标的表。我们还可以调整批处理大小以提高性能。

在上图 6.12 所示的 Upsert 目标对话框的“列映射”选项卡中，我们将映射所有的源列，并在 CampaignID、CountryCode 和 EventTime 上勾选“键”选项。键列指定了在决定是插入行还是进行更新时用于匹配行的业务键。

在对话框的高级页面中，我们可以配置在插入前后执行的命令。这对于创建和删除将支持加载的索引非常有用。它也可以用于指定提示，例如施加表锁。

在服务器之间执行 ETL 操作时，使用数据流总是一种好主意，而不是将 SSIS 作为执行 T-SQL 脚本的编排工具。在同一服务器上的表之间执行 ETL 操作时，SSIS 的性能可能比 T-SQL 脚本慢；然而，如果我们需要开箱即用的日志记录、错误处理和调试功能，以及能够使单行失败的能力，它仍可能是一个更好的选择。

错误25# 在我们只需要一个子集时提取所有数据

在构建数据流时，总是将数据流源配置为从源表中读取所有数据可能很诱人。提取整个表是默认选项。在下拉框中选择我们的表，然后开始操作。

在某些场景下，我们确实需要从表中提取和处理所有数据，如果是这种情况，那么使用这个选项没有问题。然而，如果我们只需要表中的一部分数据，那么这样做可能会对性能产生巨大影响。以我们的合并聚合数据流为例。在前一节中，我们将数据流源配置为仅从 marketing.Impressions 表中提取所有数据，如图 6.13 所示。

如果我在我的测试设备上执行数据流任务，它需要16.403秒才能完成。

然而，如果我们检查数据流，我们会注意到在数据流中我们唯一需要的列是 CampaignID、CountryCode、EventTime、BidPrice 和 CostPerMille。如果我们删除其他不必要的列，那么缓冲区将更小，性能也会更高。

我们还可以设想有一条业务规则，这意味着仅为过去 30 天提供回溯的展示数据。因此，我们知道任何超过 30 天的数据都不会发生变化，因此总计数据无需重新处理。我们可以通过在源头对数据进行过滤来减少数据流需要处理的数据缓冲区的数量。

为了实现这一点，让我们重新配置我们的数据流源，使用命令加载数据，而不是从表中提取所有数据。重新配置的数据流如图6.14所示。

此数据流源中使用的查询可以在清单6.7中找到。该查询仅提取所需的列并过滤数据，以便仅将事件时间在过去30天内的展示拉入数据流中。

SELECT CampaignID, CountryCode, BidPrice, CostPerMille, EventTime FROM marketing.Impressions WHERE (EventTime >= GETDATE() – 30) ;

当我在测试平台上使用新配置执行数据流时，它在2.937秒内完成。如果我们的ETL时间窗口很紧，通过在整个包中实现这样的性能提升，可能会带来巨大的差异。

提示：由于过滤器是基于日期的，如果你将来按照此示例操作，很可能不会返回任何行。你可以通过更新基础表中的事件时间或修改数据流源查询中的 WHERE 子句来解决此问题。

如果我们不需要来自源的所有数据，那么我们应该在源头进行筛选，以减少在我们的流程中所需的数据缓冲区的大小和数量。

总结

1. 从外部来源加载数据时，我们应该尽量保留尽可能多的数据。可以考虑使用具有广泛列类型的暂存表，并使用重定向行功能将错误行导入错误处理表中。
2. 调整数据流设置，例如 MaximumRowsPerBuffer，可能会对性能产生显著影响。这一点尤其重要，特别是在 ETL 时间窗口紧张时。
3. 在将数据加载到 SQL Server 时，应尽可能使用 OLE DB 数据源和目标以提高性能。始终调整 OLE DB 目标快速加载选项，以优化您环境下的性能。
4. 没有万能的配置。测试各种配置以了解哪种在特定环境中表现最佳。
5. 避免将 SSIS 用作执行 T-SQL 任务的编排工具。这样做会失去使用 SSIS 的许多好处，例如开箱即用的日志记录和错误处理。
6. 除非确实需要，否则避免从表中提取所有数据。通过限制返回的列数和行数以匹配数据流所需的内容，我们可以显著提高包的性能。如果你不需要表中的所有数据，请使用带有 SQL 命令的数据流源，并在源端过滤数据。