SQL Server 2000中的数据转换服务 (DTS)

SQL Server 2000中的数据转换服务 (DTS)

更新日期： 2004年06月15日

发布者 Diane Larsen

Euan Garden, 投稿人

Microsoft Corporation

2000 年 9 月

摘要： 为了完成数据合并、存档和分析等任务；为了进行应用程序开发；为了进行数据库或服务器升级，数据库管理员经常需要导入、导出以及转换数据。SQL Server 2000 中的数据转换服务（DTS）为此提供了一组图形化工具和可编程对象，能够帮助管理员和开发人员解决从不同来源到单个或多个目标的数据转移问题，包括数据提取、转换以及合并。您可以将任务、工作流操作和限制条件组成 DTS数据包，然后安排定期或在特定事件发生时执行该数据包。本白皮书将介绍 DTS，给出一些能够用于创建 DTS 解决方案的组件和服务，介绍如何使用 DTS Designer 来实施 DTS 解决方案，最后将介绍 DTS 应用程序开发。

本页内容

	DTS 简介
	使用 DTS Designer
	保存 DTS 数据包的选项
	将 DTS 作为应用程序开发平台

DTS 简介

大部分公司的数据都具有多种存储格式和存储位置。为了支持决策制定、提高系统性能或升级现有系统，经常必须将数据从一个数据存储位置转移到另一个位置。

Microsoft SQL Server 2000 数据转换服务（DTS）为此提供了一系列的工具，您可以用来从不同来源将数据提取、转换和合并到单个或多个目标。通过使用DTS工具，您可以根据公司的特殊需求创建定制的数据移动解决方案，正如下面这些情形：

•	您已经在早期版本的 SQL Server 或其他平台（例如 Microsoft Access）上部署了一个数据库应用程序。现在，新版本的应用程序需要 SQL Server 2000，而且需要更改数据库架构，并转换部分数据类型。
•	为了复制和转换数据，可以构建一个 DTS 解决方案，将数据库对象从原始数据源复制到 SQL Server 2000 数据库中，同时重新设置数据栏并更改数据类型。您可以使用 DTS 工具来运行这个解决方案，或者将这个解决方案嵌入到您的应用程序中。
•	您必须将一些关键的 Microsoft Excel 电子表格合并到 SQL Server 数据库中。很多部门在每月底创建电子表格，但是没有设置日程安排来完成所有的电子表格。
•	为了合并电子表格数据，您可以构建一个 DTS 解决方案，使其在消息被发送到消息队列时执行。这个消息将触发 DTS，使其从电子表格中提取数据，执行各种定义的转换，然后将数据装载到 SQL Server 数据库中。
•	您的数据仓库中保存了有关业务操作的历史数据，您要使用 Microsoft SQL Server 2000 分析服务来汇总这些数据。这个数据仓库需要每天夜间从联机事务处理（OLTP）数据库进行更新。而您的 OLTP 系统一天 24 小时都在使用中，其性能十分关键。 您可以构建一个 DTS 解决方案，使用文件传输协议（FTP）将数据文件移动到本地驱动器中，将数据装载到事实表中，然后使用分析服务对数据进行统计。您可以安排这个 DTS 解决方案每天夜间执行，也可以使用新的 DTS 日志选项来跟踪这个过程所用的时间，使您能够分析性能随时间的变化。

DTS 是什么?

DTS 是一组数据转换工具，您可以用来在一个或多个数据源（例如 Microsoft SQL Server、 Microsoft Excel 或 Microsoft Access）之间进行不同类型数据的导入、导出和转换。其中的连通性通过数据访问的开放式标准－OLE DB－来提供。ODBC（开放式数据库连接）数据源由 OLE DB Provider for ODBC 来支持。

您可以将 DTS 解决方案创建为一个或多个数据包。每个数据包中可以包含一组有序的任务，定义所要执行的工作，也可以包含数据和对象的转换、定义任务执行的工作流限制以及数据源和目标的连接等。DTS 数据包也提供记录数据包执行细节、控制事务以及处理全局变量等服务。

下列工具可以用于创建和执行 DTS 数据包：

•	导入/导出向导（Import/Export Wizard）用于构建相对简单的 DTS 数据包，支持数据迁移和简单转换。
•	DTS Designer 图形化地实施 DTS 对象模型，允许您创建具有大量功能的 DTS 数据包。
•	DTSRun是一个命令提示符实用程序，用来执行已有的 DTS 数据包。
•	DTSRunUI 是DTSRun的图形化界面, 也允许传递全局变量和生成命令行。
•	SQLAgent 不是一个 DTS 应用程序；但是 DTS 可以用它来安排数据包的执行。

您也可以使用 DTS 对象模型通过编程创建和运行数据包，构建定制任务以及构建定制转换。

DTS 中的新内容?

Microsoft SQL Server 2000 引入了许多 DTS 增强和新特性：

•	新的 DTS 任务包括 FTP 任务、执行数据包任务、动态属性任务以及消息队列任务。
•	增强的日志功能记录了每个数据包执行的信息，允许您拥有完整的执行历史，并能查看任务中每个步骤的信息。您可以生成异常文件，包含可能由于错误而没有执行的数据行。
•	您可以将 DTS 数据包保存为 Microsoft Visual Basic 文件。
•	新的多段数据泵允许高级用户在不同阶段定制数据转换操作。同时，也可以使用全局变量作为查询的输入参数。
•	您可以在 DTS 转换任务和执行 SQL 任务中使用参数化的源查询。
•	您可以使用执行数据包任务，动态地将全局变量的取值从父数据包分配到子数据包。

返回页首

使用 DTS Designer

DTS Designer 图形化地实施 DTS 对象模型，允许您图形化地创建 DTS 数据包。您可以使用 DTS Designer 来：

•	创建包含一个或多个步骤的简单数据包。
•	创建包含复杂工作流的数据包，这些工作流中可包含使用有条件逻辑的多步操作、事件驱动的代码或多个数据源的连接。
•	编辑已有的数据包。

DTS Designer 界面由工作区域、工具栏和菜单组成。其中工作区域用于构建数据包，工具栏包含有数据包元素，您可以将它们拖动到设计页中，菜单中包含有工作流和数据包管理命令。

图1：DTS Designer 界面
查看大图。

在 DTS Designer 中，您可以将连接和任务拖动到设计页中，并指定工作流执行的顺序，从而轻松地创建功能强大的 DTS 数据包。下面的内容将定义任务、工作流、连接和转换，并介绍如何使用 DTS Designer 轻松地实施 DTS 解决方案。

任务：定义数据包中的步骤

DTS 数据包中通常包含一个或多个步骤。每个任务定义了一个可能在数据包执行过程中执行的工作项目。您可以使用任务来：

•

转换数据

	转换数据任务	用来将数据从来源移动到目标，可以选择在数据上应用数据栏转换。
	数据驱动的查询任务	用来对数据执行基于 Transact-SQL 的灵活操作，包括存储过程以及 INSERT、UPDATE 或 DELETE 声明。
?	平行数据泵任务 (1)	仅可用于编程，平行数据泵任务执行与转换数据和数据驱动的查询任务相同的功能，但是支持 OLE DB 2.5 及更新版本所定义的 “章节式” 数据行集。

•

复制和管理数据

	批量插入任务	用于快速地将批量数据装载到 SQL Server 表或视图中。
	Execute SQL 任务	用于在数据包执行过程中运行 SQL 语句。Execute SQL 任务也可以保存查询的结果数据。
	Copy SQL Server Objects 任务	用来将 SQL Server 对象从一个 SQL Server 安装或实例中复制到另一个中。您可以复制数据和表等对象，也可以复制视图和存储过程等对象定义。
	Transfer Database 任务(1)	用来将 SQL Server 数据库从一个 SQL Server 7.0 或 SQL Server 2000 实例移动或复制到 SQL Server 2000 实例中。
	Transfer Error Messages 任务(1)	用于将用户指定的错误消息从 SQL Server 7.0 或 SQL Server 2000 实例复制到 SQL Server 2000 实例，这些错误消息是由sp_addmessage系统存储过程所生成的。
	Transfer Logins 任务(1)	用来将帐号从 SQL Server 7.0 或 SQL Server 2000 实例复制到 SQL Server 2000 实例。
	Transfer Jobs 任务(1)	用来将作业从 SQL Server 7.0 或 SQL Server 2000 实例复制到 SQL Server 2000 实例。
	Transfer Master Stored Procedures 任务(1)	用来将存储过程从 SQL Server 7.0 或 SQL Server 2000 实例中的master数据库复制到 SQL Server 2000 实例的 master数据库。

•

从数据包中将任务作为作业运行

	ActiveX Script 任务	用来编写代码，执行其他 DTS 任务中没有的功能。
	Dynamic Properties 任务(1)	用于在数据包运行时从 DTS 数据包外的来源处获得数值，并将这些值分配给选定的数据包属性。
	Execute Package 任务(1)	用于从数据包中运行其他 DTS 数据包。
	Execute Process 任务	用于运行可执行程序或批处理文件。
	FTP 任务 (1)	用来从远程服务器或 Internet 位置下载数据文件。
	Message Queue 任务(1)	用来从 Microsoft 消息队列发送和接受消息。
	Send Mail 任务	用来发送电子邮件。
	Analysis Services Processing 任务 (2)	用于处理一个或多个 SQL Server 2000 分析服务中所定义的对象。
	Data Mining 任务 (1,2)	用于从 SQL Server 2000 分析服务所定义的数据挖掘模型对象中创建一个预测查询和输出表。

1 SQL Server 2000中新增。

2 仅适用于已安装 SQL Server 2000 分析服务的情形。

您可以程序化地创建定制任务，然后使用 Register Custom Task（注册定制任务）命令将它们集成到 DTS Designer 中。

为了说明这些任务的使用，在这里我们给出了一个包含两个任务的简单 DTS 数据包： Microsoft ActiveX Script 任务和 Send Mail 任务：

图2：具有两个任务的 DTS 数据包

ActiveX Script 任务可以驻留任何 ActiveX 脚本引擎，包括 Microsoft Visual Basic Scripting Edition (VBScript)、Microsoft JScript 或者 ActiveState ActivePerl（您可以从http://www.activestate.com/下载）。 Send Mail 任务可以发送消息，指出该数据包已经运行。请注意，这些任务是没有顺序的。在执行数据包时，ActiveX Script 任务和 Send Mail 任务同时运行。

工作流：设置任务优先级

当您在定义一组任务时，通常任务的执行是应该有一定顺序的。如果这些任务拥有一定的顺序，那么每个任务将称为一个过程中的一个步骤。在 DTS Designer 中，您可以在 DTS Designer 设计页中对任务进行操作，使用优先级限制来控制任务执行的顺序。

优先级限制将数据包中的任务依次链接起来。下表给出了您可以在 DTS 中使用的优先级限制的类型。

优先级限制	说明
On Completion（完成后） (蓝色箭头)	如果您希望任务 2 处于等待状态，直至任务 1 完成（无论结果如何），那么就使用 On Completion 优先级限制将任务 1 链接到任务 2。
On Success（成功后） (绿色箭头)	如果您希望任务 2 处于等待状态，直至任务 1 成功完成，那么就使用 On Success 优先级限制将任务 1 链接到任务 2。
On Failure（失败后） (红色箭头)	如果您希望任务 2 仅在任务 1 无法成功执行时才开始执行，那么就使用 On Failure 优先级限制将任务 1 链接到任务 2。

下图给出了具有 On Completion 优先级限制的 ActiveX Script 任务和 Send Mail 任务。当 ActiveX Script 任务完成后（不论成功还是失败），Send Mail 任务都开始运行。

图3：具有 On Completion 优先级限制的 ActiveX Script 任务和 Send Mail 任务

您可以配置不同的 Send Mail 任务，一个用于 On Success 限制，另一个用于 On Failure 限制。这两个 Send Mail 任务可以根据 ActiveX Script 的成功或失败来发送不同的邮件。

图4：邮件任务

您也可以在一个任务上应用多个优先级限制。例如， Send Mail 任务"Admin Notification"可以具有来自脚本#1的 On Success 限制和来自脚本#2 的 On Failure 限制。在这种情况下，DTS 认为其使用逻辑"AND"关系。因此为了发送 Admin Notification 邮件，脚本#1必须成功执行，而脚本#2 必须失败。

图5：同一任务多个优先级限制的示例

连接：访问和移动数据

为了成功地执行复制和转换数据的 DTS 任务，DTS 数据包必须与其来源和目标之间建立有效的连接，同样需要连接到其他数据源（例如查询表）

在创建数据包时，您可以从有效 OLE DB 提供商和 ODBC 驱动程序列表中选择连接类型，对连接进行配置。可用的连接类型包括：

•

Microsoft 数据访问组件(MDAC)驱动程序

	Microsoft OLE DB Provider for SQL Server
	Microsoft 数据链接
	Microsoft ODBC Driver for Oracle

•

Microsoft Jet 驱动程序

	dBase 5
	Microsoft Access
	HTML 文件（来源）
	Microsoft Excel 97-2000
	Paradox 5.X

•

其他驱动程序

	文本文件（来源）
	文本文件（目标）
	其他连接

DTS 允许您使用任何 OLE DB 连接。连接工具栏中的图标为常用连接提供了方便的访问方式。

下图介绍了一个具有两个连接的数据包。数据被从一个 Access 数据库（来源连接）复制到 SQL Server 生产数据库（目标连接）。

图6：具有两个连接的数据包示例
查看大图。

这个数据包的第一步是一个执行 SQL 任务，该任务检查是否已经存在目标表。如果已经存在，这个表将被删除并重新创建。在成功的完成了执行 SQL 任务后，数据在第二步中被复制到 SQL Server 数据库。如果复制操作失败，则在第三步中发送一封电子邮件。

数据泵：转换数据

DTS 数据泵是一个 DTS 对象，用来驱动数据的导入、导出和转换。在转换数据、数据驱动的查询以及平行数据泵任务中将使用这个数据泵。这些任务将在来源和目标连接中创建数据行组，然后创建数据泵实例，将数据行在来源和目标之间移动。在数据行被复制时，对每一行进行数据转换。

下图的步骤 2 中，在 Access DB 任务和 SQL Production DB 任务之间使用了一个转换数据任务。转换数据任务是两个连接之间的灰色箭头。

图7：转换数据任务的示例
查看大图。

为了定义从来源连接收集到的数据，您可以为这个转换任务创建一个查询。DTS 支持参数化的查询，允许您在查询执行时定义查询值。

您可以在该任务的属性对话框中键入这个查询。或者使用数据转换服务查询设计器（Data Transformation Services Query Designer），该工具可以用来为 DTS 任务图形化地创建查询。下图中，使用查询设计器构建了一个将三个表加入到pubs数据库中的查询。

图8：数据转换服务查询设计器界面
查看大图。

在转换任务中，您也可以定义对数据做出的更改。下表解释了 DTS 提供的内置转换功能。

转换	说明
复制数据栏	用来直接将数据从来源复制到目标数据栏中，对数据不进行任何转换。
ActiveX 脚本	用来构建定制的转换。请注意，由于转换是逐行进行的，因此ActiveX 脚本可能会影响 DTS 数据包的执行速度。
日期事件字符串	用来将来源数据栏中的日期或事件转换为目标数据栏中不同的格式。
小写字母字符串	用来将来源数据栏中的小写字母转换（如果需要）为目标数据栏的数据类型。
大写字母字符串	用来将来源数据栏中的所有大写字母转换（如果需要）为目标数据栏的数据类型。
字符串中段	用来从来源数据栏中提取子字符串，将其转换，然后将结果复制到目标数据栏中。
修剪字符串	用于删除来源数据栏中字符串前、后和中间的空白，并将结果复制到目标数据栏中。
读取文件	用来打开来源数据栏中所指定的文件的内容，并将其内容复制到目标数据栏中。
写入文件	用来将来源数据栏（数据）的内容复制到文件中，该文件的路径由第二个来源数据栏（文件名）指定。

您也可以通过编程创建自己的定制转换。创建定制转换的最快方法是使用活动模板库（Active Template Library，ATL）定制转换模板，该模板包含在 SQL Server 2000 DTS 示例程序中。

数据泵错误日志

SQL Server 2000中 拥有一种记录转换错误的新方法。您可以定义三种异常日志文件，用于数据包执行过程：错误文本文件、来源错误数据行文件以及目标错误数据行文件。

•	常规错误信息被写入到错误文本文件中。
•	如果转换过程失败，那么来源数据行将出现错误，并将该行写入到来源错误数据行文件中。
•	如果插入过程失败，那么目标数据行将出现错误，并将该行写入到目标错误数据行文件中。

异常日志文件被定义在转换数据的任务中。每个转换任务可以拥有它自己的日志文件。

数据泵阶段

在默认情况下，数据泵只有一个阶段：数据行转换。这个阶段就是您所配置的在转换数据任务、数据驱动的查询任务以及平行数据泵任务中的数据栏转换，而不选择阶段。

多数据泵阶段功能是 SQL Server 2000 中所新增的。通过在 SQL Server Enterprise Manager 中选中多段数据泵选项，您可以在操作过程中的不同地方访问数据泵，添加功能。

在将一行数据从来源复制到目标时，数据泵按照下图所示的基本程序进行操作。

图9：数据泵过程
查看大图。

在数据泵处理完最后一行数据后，任务完成，数据泵操作结束。

如果高级用户需要在数据包中添加功能，使其支持任何数据泵阶段，他可以这样做：

•	为每个定制的数据泵阶段编写一个ActiveX脚本阶段。如果您使用ActiveX脚本功能来定制数据泵阶段，不需要任何数据包以外的代码。
•	在 Microsoft Visual C++ 中创建 COM 对象，定制所选中的数据泵阶段。您在数据包以外开发这个程序，转换的每个所选中的阶段都将调用这个程序。与访问数据泵阶段的 ActieX 脚本方法不同的是，ActiveX 脚本方法为每个选中的阶段使用不同的功能和入口点，而这种方法提供了单一入口点，由多个数据泵阶段在任务执行过程中调用。

返回页首

保存 DTS 数据包的选项

下列选项可以保存 DTS 数据包：

•	Microsoft SQL Server 如果您希望在任何网络中的 SQL Server 实例中保存数据包，请将您的 DTS 数据包保存在 Microsoft SQL Server 上，并保留这些数据包的清单，在数据包开发过程中添加和删除数据包版本。
•	SQL Server 2000 元数据服务 如果您计划跟踪数据包版本、元数据和数据血统信息，请将 DTS 数据包保存在元数据服务上。
•	结构化的存储文件 如果您需要在网络中复制、移动和发送数据包，而不想把数据包存储到 Microsoft SQL Server 数据库中，请将 DTS 数据包保存为结构化的存储文件。
•	Microsoft Visual Basic 如果您希望将其集成到 Visual Basic 程序中，或作为 DTS 应用程序开发的原型，请将由 DTS Designer 和 DTS 导入/导出向导创建的 DTS 数据包保存为 Microsoft Visual Basic 文件。

返回页首

将 DTS 作为应用程序开发平台

DTS Designer 提供了多种数据移动任务的解决方案。DTS 通过提供对 DTS 对象模型的程序化访问，扩展了许多可用的解决方案。使用Microsoft Visual Basic、Microsoft Visual C++ 或其他支持 COM 的应用程序开发系统 ，您可以为您的环境开发一个定制的 DTS 解决方案，使用图形工具中所不支持的功能。

DTS 以多种不同的方式为开发人员提供支持：

•	构建数据包 您可以开发极其复杂的数据包，访问对象模型中的所有功能，而不需要使用 DTS Designer 或 DTS 导入/导出向导。
•	扩展数据包 您可以通过定制的任务和转换来增加新的功能，这些任务和转换是专门为您的业务定制的，并且能在DTS中重复使用。
•	执行数据包 DTS 数据包的执行不需要基于任何所提供的工具，可以通过 COM 事件通过编程执行 DTS 数据包和显示过程，允许构建嵌入式或定制的 DTS 执行环境。

所提供的 DTS 程序示例能够帮助您了解 DTS 编程。这些示例与 SQL Server 2000 一同安装。

如果您要开发 DTS 应用程序，那么您可以重新分配 DTS 文件。更多信息，请参考 SQL Server 2000 光盘的 Redist.txt 文件。