SSIS OLE DB目标中的数据访问模式-SQL命令与表或视图

迪鲁宾
160 阅读6分钟

这篇文章比较了SSIS OLE DB Destination SQL命令与 "表或视图 "数据访问模式。在之前发表的文章《SSIS OLE DB Destination vs SQL Server Destination》系列中，我们解释了SSIS中的OLE DB Destination组件，并说明了它与SQL Server Destination组件的区别。因此，在这篇文章中，我们将专注于OLE DB Destination的数据访问模式，而不是该组件本身。

简介

正如OLE DB Destination官方文档中提到的，OLE DB目的地为加载数据提供了五种不同的数据访问模式。

  1. 表或视图：选择一个现有的表或视图
  2. 表或视图-快速加载： 选择一个现有的表或视图-使用快速加载选项
  3. 表名或视图名变量：选择一个包含表或视图名的变量
  4. 表名或视图名变量-快速加载： 选择一个包含表或视图名的变量-使用快速加载选项
  5. SQL命令。 一个SQL语句的结果

我们可以注意到，前四个选项是用来从数据库中选择一个现有的表或视图。区别在于名称是存储在一个变量中还是手动传递，以及是否使用快速加载选项进行数据插入。此外，SSIS OLE DB Destination SQL Command数据访问模式也与其他选项不同。

本文将解释用户应该使用SSIS OLE DB Destination SQL Command数据访问模式的用例，它与其他模式的不同之处，以及它的局限。

本文是SSIS功能面对面系列的第十五篇，旨在比较类似的SSIS组件，以消除使用该技术的数据集成开发人员的困惑。

  • 注意：本文中的所有例子都是使用Stack overflow 2013数据库、SQL Server 2019、Visual Studio 2019、SQL Server集成服务项目扩展3.4版本制作的。*

首先，让我们先简单说明一下四种 "表和视图 "的数据访问模式。

快速加载

简而言之，快速加载模式意味着在向OLE DB目的地插入数据时使用BULK INSERT操作。从性能的角度来看，快速加载是非常值得推荐的，因为数据是分批插入的，而不是逐行插入的。但是万一你需要进行逐行插入以改善错误处理和日志记录，你不必使用这个选项。

- OLE DB Destination fast load modes

图1 - OLE DB目的地快速加载模式

关于其他信息，你可以参考我们之前在这个系列中发表的SSIS OLE DB Destination Vs. SQL Server Destination文章，在那里我们解释了 "快速加载 "模式和所有相关配置。

SSIS OLE DB Destination fast load options

图2 - SSIS OLE DB 目的地快速加载选项

手动选择表与使用变量

有两种方法可以将目标表名传递给SSIS OLE DB Destination。

  1. 从一个下拉列表中手动选择名称
  2. 从一个变量中选择名称

从下拉列表中选择表名是一旦我们导入数据到一个固定的表名时使用的模式，这主要是在定期的数据仓库过程中进行的，因为维度和事实表名是众所周知的，不会经常改变。

当使用这个选项时，用户可以在集成服务设计工作室（Visual Studio）中通过点击OLE DB目的地编辑器中出现的 "新建 "按钮来创建一个新的表。

Creating a new destination table button

图 3 - 创建一个新的目标表按钮

一旦用户点击 "新建 "按钮，就会出现一个小的SQL命令窗口，包含根据OLE DB目的地输入数据流元数据生成的CREATE TABLE命令。用户可以编辑这个命令。

CREATE TABLE SQL Command

图4 - CREATE TABLE SQL命令

点击 "OK "按钮后，创建的表名出现在表名下拉列表中。

视图作为一个目标？

在SQL中，视图被认为是一个虚拟表。它是存储在SQL数据库引擎中的一个固定查询的结果集。主要的想法是，用户可以把这个结果集作为一个单一的对象来查询。在此基础上，如何将数据插入到虚拟表中？向视图中插入数据将修改存储在视图的底层表中的数据。

根据SQL Server View的官方文档，并非所有的视图都是可更新的。有很多条件允许开发人员修改存储在视图的底层表中的数据。

  • 任何修改，包括UPDATE、INSERT和DELETE语句，必须只引用一个基础表中的列
  • 视图中被修改的列必须直接引用表列中的基础数据。这些列不能以任何其他方式派生，例如聚合（AVG、COUNT、SUM...）和计算（表达式、UNION、CROSS JOIN...）。
  • 被修改的列不会受到GROUP BY、HAVING或DISTINCT条款的影响
  • 在视图的select_statement中的任何地方都没有使用TOP和WITH CHECK OPTION子句。

SSIS OLE DB目的地SQL命令的数据访问模式

SSIS OLE DB Destination SQL Command与使用视图作为目标非常相似。它们都需要我们之前已经列出的相同条件。当目标表包含太多的列，而你只需要选择一些特定的列时，必须主要使用SQL命令。

如下图所示，当使用SSIS OLE DB Destination SQL Command数据访问模式时，目标编辑器有五个属性/功能。

SSIS OLE DB Destination SQL Command configuration

图 5 - SSIS OLE DB Destination SQL Command 配置

  1. SQL命令。我们应该在文本框中写下我们想用作目标的SQL查询

  2. 建立查询按钮。打开一个查询生成器，以便于建立SQL查询。

    SSIS OLE DB Destination SQL command Query builder

    图6 - 查询生成器

  3. 浏览。这个按钮允许读取存储在一个单独文件中的SQL查询。

  4. Parse Query:在写完一个SQL查询后，我们可以使用这个选项通过检查任何语法错误来验证该SQL查询。

  5. 查看现有的。 这个按钮可以预览SQL查询的结果（现有数据）。

    Previewing the existing data of a SSIS OLE DB Destination SQL Command

    图7 - 现有数据

如前所述，SSIS OLE DB Destination SQL Commands不允许连接或任何复杂的计算或聚合。为了证明，我们运行了下面的例子。

我们创建了一个有一个数据流任务的简单包。在这个数据流任务中，我们添加了一个OLE DB Source和一个OLE DB Destination。我们使用下面的查询作为SSIS OLE DB目的地SQL命令。

SELECT Users.AboutMe, Users.Age, Comments.Score, Comments.CreationDate
FROM Users INNER JOIN Comments ON Users.ID = Comments.UserID

在执行该包后，它抛出了以下异常。

[OLE DB目的地[2]]错误。SSIS错误代码DTS_E_OLEDBERROR。发生了一个OLE DB错误。错误代码。0x80004005.有一条OLE DB记录可用。来源。"Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005描述。"不能从多个表中插入或更新列"。

Exception when using joins in the SSIS OLE DB Destination SQL command

图8 - 在SSIS OLE DB目的地SQL命令中使用连接时的异常情况

使用SSIS OLE DB Destination SQL命令的主要限制是，它使用逐行插入的方式，性能很慢。此外，它的主要优点是在配置目标组件时减少了需要的列映射数量。

总结

这篇文章解释了SSIS中OLE DB Destination组件的不同数据访问模式。我们之前说明了快速加载选项和使用视图作为目标所需的条件。最后，我们说明了SSIS OLE DB Destination SQL命令的数据访问模式，它与使用表或视图有什么不同，以及它的限制。

avatar
文章
阅读
粉丝