SSIS学习使用二：最基本的数据流任务（处理流程）

代码迷途
2,117 阅读10分钟

这是我参与11月更文挑战的第5天，活动详情查看：2021最后一次更文挑战

通过一个完整的 SSIS 实际项目，介绍其基本使用和处理流程。当然，首先是 SSIS 的安装以及 SSIS 项目的创建。

安装SSIS

要确保安装了集成服务。

在SQL Server 2012安装过程中，特性（功能选择）下，选中安装 Integration Services 和 SQL Server Data Tools(SSDT，可根据需要安装) 【SQL2012之前是Business Intelligence Development Studio】

还可以在 Visual studio 中安装SSDT，或独立安装。

创建SSIS项目

在开始菜单中，找到 SQL Server 下面的SQL Server Data Tools，打开并运行。

初次运行时会让你选择使用哪种环境设置。包括有 Business Intelligence Settings、General Development Settions、Visual C# Development Settings 等。通常选择 Business Intelligence Settings 即可。

欢迎页面点击"新建项目"，或，在文件下—>新建—>项目。选择 Integration Services 模板，创建SSIS项目，命名为FirstSSIS。

一个空的SSIS项目即创建完成。

SSIS数据泵

SSIS 创建的初衷是用来移动数据，其中的数据流任务（Data Flow Task）提供该功能。

数据流任务

先从一个数据流任务的基础开始：

图1

上图1是一个非常简单的数据流任务：数据通过 OLE DB源适配器 从一个数据库读取到数据流任务，并通过一个 OLE DB目标适配器 写入到一个目标数据库。源和目标适配器通过连接管理器和数据库或其他数据存储进行交互。

图1中有三个对象：

  1. OLE DB源适配器
  2. OLE DB目标适配器
  3. 连接OLE DB源和OLE DB目标适配器的数据流路径(Data Flow Path)（绿色箭头）

连接管理器和OLE DB源/目标适配器

关于连接管理器(Connection Managers)最重要的事情是：连接管理器用来桥接(连接)SSIS包和外部数据源。连接管理器处理如服务器名、数据库实例(如果可用)、数据库名和认证等之类的事情。OLE DB源/目标适配器处理如表和列之类的事情。

OLE DB源适配器在连接管理器提供的凭据的上下文中，对在连接管理器中配置的服务器/数据库执行查询。几个重要的注意事项：

首先，SSIS永远不会存储或保存解密的密码字段。如果你的连接需要密码，并且选中了“保存我的密码”（或“保存密码”）复选框，那么SSIS包将被加密。

如果使用Windows身份验证，则 SSIS 将在内部存储密码并对其加密，连接管理器将在执行包的用户的上下文中连接到数据库。

OLE DB目标数据库也作为数据流任务和连接管理器的一个接口，进入 OLE DB目标适配器 的每条数据被写入 OLE DB目标编辑器 指定的目标数据表中。

OLE DB源适配器将数据带入数据流任务。 OLE DB目标适配器将从数据流任务中输出的数据写入到目标数据库表中。

数据管道(Pipeline)

数据流路径在一个数据流任务内连接源适配器、转换和目标适配器。

上面图1简单的数据流任务中没有转换 —— 因此数据流路径直接从 OLEDB源适配器 传输数据到 OLEDB目标适配器。

数据流任务中的所有组件都对 数据的行 进行操作。行是基本单位。行被分组进缓冲区，缓冲区用于在数据“管道”中移动行。之所以称为管道，是因为行流入，通过，然后流出数据流任务的过程，很像管道的流通。

数据被分块读取到 OLE DB源适配器 中。单个数据块填充一个缓冲区。数据缓冲区会先处理，然后数据移动到“下游”。

开启数据流任务

添加数据流任务

打开上面创建的SSIS项目 FirstSSIS。从工具箱(toolbox)中，拖拽一个数据流任务到控制流(Control Flow)画布中。

数据流任务是一个控制流任务（The Data Flow Task is a Control Flow task）。这一点很重要，因为当你打开数据流选项卡（Data Flow tab)时，可以看到在控制流和数据流任务之间有许多相似之处，如工具箱，绿色、红色箭头连接器等。这也很容易产生困惑。

数据流任务名字可以通过双击修改。

右键数据流任务——编辑(Edit)，可以发现，会打开数据流选项卡 —— 它是数据流任务编辑器。

添加源适配器

从工具箱中拖拽一个 OLE DB源适配器 到画布

当添加一个OLEDB源到数据流任务，组件会显示一个错误图标。可以通过查看错误列表 Error List 获取详细信息。在菜单栏视图 View 中，点击错误列表Error List

SSIS会对操作自动验证。错误列表包含有关SSIS错误和警告的详细信息。

在错误列表中，可以看到数据流任务中，未给 OLE DB源 分配连接管理器。

源适配器添加连接器

  • 添加连接器

双击 OLE DB源适配器（双击图标而不是文本部分），或右键选择编辑Edit...，打开OLE DB源编辑器。

在OLE DB源编辑器中可以选择已有的连接器，或者新建。

新建连接器，可以添加需要的连接，然后选择：

  • 源数据导入的方式

选择连接器后回到 OLEDB源编辑器，如下，有一个警告信息："Select a table of view from the list."(从列表中选择表或视图)

这就需要决定如何引入数据？通过数据访问模式(Data Access Mode)的属性可以指定 OLEDB源适配器 如何引入数据。

表或视图(Table or view)：允许从连接管理器中配置的数据库中选择一个表或视图作为数据源。这是默认选项，如果选择使用此选项从源数据库获取数据，则只需从“数据访问模式”下拉列表下方的“表或视图的名称”(Name of the table or view)下拉列表中选择表或视图名称：

表名变量或视图名变量(Table name or view name variable)：你可以保存表名或视图名为一个SSIS变量。这样为动态源表提供了灵活性。但是有一点需要记住，OLE DB源适配器在设计时是和数据流任务"耦合"的。这意味着，你可以改变table的名字，而不能是列名或其数据类型。因此在单个数据流中加载不同的架构(schema)的表是一个常见的误解

SQL命令和变量中的SQL命令(SQL Command and SQL Command From Variable)：提供了一种使用SQL语句查询 连接管理器下拉列表中配置的数据库 的方法。比如从一个表或视图，或连接的多个表或视图中获取某几列。选择该项后，下面会出现 SQL Command Text 文本框

选择SQL命令后，需要填写SQL语句，否则下面会有"编写SQL语句"的提示，此处设置如下：

点击"生成查询"(Build Query)按钮，可以使用可视化的方式构建SQL查询。

查询生成器工具(Query Builder utility)

  • 源数据导入的列

在 OLE DB源编辑器 的左侧 "列"(Columns) 这一页中，可以看到在连接管理器中由数据访问模式Data Access Mode提供的列

"可用外部列"(Available External Columns)：是从源适配器输出到"下游"(转换或目标表)的列，如果不希望输出某列的数据，应该在上面"连接管理器"中的语句里取消，避免数据冗余和浪费。

下面的 "输出列"(Output Column) 可以定义列别名。这一点可以可以直接在SQL语句中使用 as 实现。

之后点击"确定"(OK)。

OLE DB目标适配器

  • 添加 OLE DB目标适配器 到数据流任务画布中

添加目标适配器后，需要给目标适配器一个输入，目标适配器需要接受一个输入，然后将输入的数据写入到目标中。

点击“OLE DB源”，然后将底部的绿色箭头拖拽到“OLE DB目标”上。如下：

不添加输入，直接编辑 目标适配器 时会有提示如下：

可以修改目标适配器的名字为"FullName"。

  • 数据流路径(Data Flow Path)

右键连接的数据流路径，选择"编辑"Edit。

在数据流路径编辑器的"元数据"(Metadata)页中，显示了连接数据流组件(Data Flow components)的数据管道的内容。

  • OLE DB目标编辑器

打开目标编辑器后，连接管理器仍然选择上面创建的连接器。

数据访问模式属性默认是"表或视图——快速加载"(Table or view – fast load)。

"表或视图——快速加载"和"表或视图"(“Table or view – fast load” and “Table or view”) 这两个选项主要的不同是：快速加载选项可以执行一个 BULK INSERT 语句，非快速加载选项("表或视图")会限制生成 INSERT INTO 语句。

如果目标表或视图存在，可以从 Name of the table or view 下拉类表中选择；如果不存在，可以点击"表或视图的名称"右侧的"新建"按钮。在弹出的“创建表”对话框中，点击确定，将会执行语句创建一个新表或视图。

新建的表名是 OLE DB目标适配器 的名字，列来自数据流路径元数据，在元数据中列的数据类型为DT_WSTR，此处语句为 nvarchar(50)，也就是 SSIS 中的 DT_WSTR 数据类型和 SQL Server 的 nvarchar 等效。

表创建后会显示在“表或视图的名称”中。

  • 特别注意

==正常或实际生产中，如果表不存在时，不应该也不能完全使用此处的新建表的命令==。原因是此处仅仅只是根据传入的Matedate数据来自动创建对应的表，但是关于表结构中字段的键、约束、索引等均没有，这样会导致后续操作错误、数据完整性等更严重的问题。

因此，此处创建表的语句仅仅只能作为参考。

  • 映射

可以看到上面底部的提示：映射 映射页上的列(Map the columns on the Mappings page)。

点击左侧的映射(Mappings)页，

可用输入列(Available Input Columns) 是从数据流路径流入到 OLE DB目标的列；可用目标列(Available Destination Columns) 是目标数据库的 FullName表 的列。由于列名和数据类型是一样的，所以 OLE DB目标编辑器 自动映射了它们。

点击确定(OK)，关闭 OLEDB目标编辑器。

测试SSIS包

点击"启动调试"(Start Debugging)或F5，测试刚才创建的 SSIS包。

运行成功后如下：

同时数据也已经转移到目标表 FullName 中

总结

本篇内容主要介绍了：

  • SSIS的安装。
  • SSIS项目的创建和使用。
  • 讨论了连接管理器和适配器之间的关系（在OLE DB源和OLE DB目标中） 。
  • 检查了将数据流任务组件相互连接并呈现“数据流管道”的数据流路径的角色和某些功能。
  • 了解有关设计时验证，以及如何查看错误和警告的信息。
  • 研究了适配器编辑器的一些很酷的省时功能。
  • 窥视了“数据流任务”的内容，查看了“数据流路径”的元数据。
  • 了解了 OLE DB目标 如何使用元数据来构建 CREATE TABLE语句。

参考

主要参考自官方文档的 Integration Services (SSIS) Packages、翻译自 The SSIS Data Pump - Level 2 of the Stairway to Integration Services 的内容等。

avatar
文章
阅读
粉丝