Microsoft SQL Server 2000 的数据转换服务

59 阅读 0 评论 39 点赞

我是靠谱客的博主等待眼神，最近开发中收集的这篇文章主要介绍Microsoft SQL Server 2000 的数据转换服务，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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 提供程序来支持 ODBC （开放式数据库连接）数据源。

可以将 DTS 解决方案创建为一个或多个软件包。每个软件包可包含一组已安排好的任务，它们定义要执行的工作、对数据和对象的转换、定义任务执行的工作流约束条件以及数据源和目标间的连接方式。 DTS 软件包还提供一些服务，如记录软件包执行详细情况、控制事务和处理全局变量。

以下工具可用于创建和执行 DTS 软件包：

· 导入 / 导出向导用于生成相对简单的 DTS 软件包，并支持数据移植和简单转换。

· DTS 设计器以图形方式来实现 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设计器

DTS 设计器以图形方式实现 DTS 对象模型，使您可以用图形方式创建 DTS 软件包。您可以使用 DTS 设计器执行以下任务：

· 创建包含一个或多个步骤的的简单软件包。

· 创建包括复杂工作流的软件包，这些工作流包括多个步骤，而这些步骤使用条件逻辑、事件驱动代码或与数据源的多种连接。

· 编辑现有软件包。

DTS 设计器界面包括一个工作区（用于生成软件包）、几个工具栏（其中包含可拖动到设计工作表上的软件包元素）以及几个菜单（其中包含工作流和软件包管理命令）。

请点击此处，查看完整的图片。

图 1. DTS 设计器界面

通过将连接和任务拖动到设计工作表，然后指定工作流的执行顺序，可以方便地使用 DTS 设计器来生成功能强大的 DTS 软件包。以下各部分将对任务、工作流、连接和转换进行定义，并阐释使用 DTS 设计器实施 DTS 解决方案的方便性。

任务：定义软件包中的步骤

一个 DTS 软件包通常包括一个或多个任务。每个任务定义一个在软件包执行期间可能要执行的工作项。可以使用这些任务进行以下操作：

· 转换数据

	转换数据任务	用于在源和目标间移动数据，可以选择对数据应用列一级的转换。
	数据驱动的查询任务	用于对数据执行灵活的基于 SQL 事务的操作，包括存储过程和 INSERT 、 UPDATE 或 DELETE 语句。
	并行数据泵任务 ¹	只可按编程方式使用，并行数据泵任务执行的功能与转换数据和数据驱动的查询任务相同，但它支持 OLE DB 2.5 或更高版本所定义的分层次的行集合。

· 复制和管理数据

	批量插入任务	用于快速将大量数据加载到 SQL Server 表或视图中。
sql.gif" border="0"/>	执行 SQL 任务	用于在软件包执行期间运行 SQL 语句。执行 SQL 任务还可以保存作为查询结果的数据。
	复制 SQL Server 对象任务	用于将 SQL Server 对象从一个 SQL Server 或其实例复制到另一个 SOL Server 或实例。可以复制对象（如：数据和表）以及对象（如：视图和存储过程）的定义。
	传输数据库任务 ¹	用于将 SQL Server 数据库从 SQL Server 7.0 版本或 SQL Server 2000 的一个实例移动或复制到 SQL Server 2000 的一个实例中。
	传输错误消息任务 ¹	用于将针对用户的错误消息（通过 sp_addmessage 系统存储过程创建）从 SQL Server 7.0 或 SQL Server 2000 的一个实例复制到 SQL Server 2000 的一个实例中。
	传输登录任务 ¹	用于将登录信息从 SQL Server 7.0 或 SQL Server 2000 的一个实例复制到 SQL Server 2000 的一个实例中。
	传输作业任务 ¹	用于将作业从 SQL Server 7.0 或 SQL Server 2000 的一个实例复制到 SQL Server 2000 的一个实例中。
	传输主存储过程任务 ¹	用于将存储过程从 SQL Server 7.0 或 SQL Server 2000 的一个实例的主数据库复制到 SQL Server 2000 的一个实例的主数据库。

· 将任务作为软件包中的作业运行

	ActiveX 脚本任务	用于编写代码以执行在其它 DTS 任务中无法执行的功能。
	动态属性任务 ¹	用于在软件包运行时从 DTS 软件包以外的源检索值，并将这些值分配给选定的软件包属性。
	执行软件包任务 ¹	用于运行软件包内其它的 DTS 软件包。
	执行进程任务	用于运行可执行程序或批处理文件。
	文件传输协议 (FTP) 任务 ¹	用于从远程服务器或 Internet 下载数据文件。
	消息队列任务 ¹	用于从 Microsoft 消息队列发送和接收消息。
	发送邮件任务	用于发送电子邮件消息。
	分析服务处理任务 ²	用于对 SQL Server 2000 分析服务中定义的一个或多个对象执行处理。
	数据挖掘任务 ^1,2	用于从 SQL Server 2000 分析服务中定义的数据挖掘模式对象中创建谓词查询或输出表。

1 SQL Server 2000 中的新功能。

2 只在安装了 SQL Server 2000 分析服务的前提下可用。

还可以用编程方式创建自定义任务，然后使用 “ 注册自定义任务 ” 命令将其集成到 DTS 设计器中。

为说明如何使用这些任务，这里显示一个简单的 DTS 软件包，其中包括两项任务： Microsoft ActiveX® 脚本任务以及发送邮件任务：

图 2. 具有两项任务的 DTS 软件包

ActiveX 脚本任务可以包含任何 ActiveX 脚本引擎，包括 Microsoft Visual Basic Scripting Edition (VBScript) 、 Microsoft JScript® 或 ActiveState ActivePerl 。这些搜索引擎可以从 http://www.activestate.com 下载。发送邮件任务可以发送消息，指示软件包已运行。请注意，这些任务尚未排序。执行软件包时， ActiveX 脚本任务和发送邮件任务将同时运行。

工作流：设置任务优先级

定义任务组时，通常有一个执行任务的顺序。如果任务已排序，每个任务将成为进程中的一个步骤。在 DTS 设计器中，在 DTS 设计器工作表上操作任务，并使用优先级约束条件来控制执行任务的顺序。

优先级约束条件继而将软件包中的任务链接起来。下表显示在 DTS 中可以使用的优先级约束条件类型。

优先级约束条件	说明
按完成顺序（蓝色箭头）	如果希望在任务 1 完成前不执行任务 2 ，而不考虑执行结果如何，则应使用 “ 按完成顺序 ” 优先级约束条件链接任务 1 和任务 2 。
按成功情况（绿色箭头）	如果希望在任务 1 成功完成前不执行任务 2 ，应使用 “ 按成功情况 ” 优先级约束条件链接任务 1 和任务 2 。
按失败情况（红色箭头）	如果希望只在任务 1 无法成功执行的情况下才执行任务 2 ，应使用 “ 按失败情况 ” 优先级约束条件链接任务 1 和任务 2 。

下图显示使用 “ 按完成顺序 ” 优先级约束条件的 ActiveX 脚本任务和发送邮件任务。当 Active X 脚本任务完成后，无论成功或失败，都将运行发送邮件任务。

图 3. 使用“ 按完成顺序” 优先级约束条件的 ActiveX 脚本任务和发送邮件任务

可以分别配置发送邮件任务，一个配置为使用 “ 按成功情况 ” 约束条件，一个配置为使用 “ 按失败情况 ” 约束条件。两个发送邮件任务可根据 ActiveX 脚本成功还是失败发送不同的消息。

图 4. 邮件任务

您还可以对一个任务采用多个优先级约束条件。例如，发送邮件任务 “ 管理通知 ” 可以在脚本 #1 采用 “ 按成功情况 ” 约束条件，在脚本 #2 采用 “ 按失败情况 ” 约束条件。在这些情况下， DTS 假设存在逻辑关系 “AND” 。因此，只有在脚本 #1 成功执行而脚本 #2 失败的情况下，才会发送 “ 管理通知 ” 消息。

图 5. 示例：对一项任务采用多个优先级约束条件

连接：访问和移动数据

要成功执行复制和转换数据的 DTS 任务， DTS 软件包必须与它的源和目标数据以及所有其它数据源（如查找表）建立有效连接。

创建软件包时，通过从可用的 OLE DB 提供程序和 ODBC 驱动程序列表选择连接类型，可对连接进行配置。可用的连接类型包括：

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

sql.gif" border="0"/>	用于 SQL Server 的 Microsoft OLE DB 提供程序
	Microsoft 数据链接
	用于 Oracle 的 Microsoft ODBC 驱动程序

· 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 对象，它驱动数据的导入、导出和转换操作。在执行数据转换、数据驱动查询和并行数据泵任务期间将使用数据泵。执行这些任务的过程是：在源和目标连接上创建行集合，然后创建数据泵实例在源和目标间移动这些行。在复制每一行时，将进行转换操作。

下图中，在第二个步骤中，转换数据任务在 Access DB 任务和 SQL Production DB 任务之间进行。转换数据任务用连接间的灰色箭头表示。

图 7. 示例：转换数据任务

要定义从源连接收集的数据，可以生成一个转换任务的查询。 DTS 支持参数化查询，参数化查询允许在执行查询时定义查询值。

可以将查询键入任务的 “ 属性 ” 对话框，也可以使用数据转换服务查询设计器，它是一个用图形化方式生成 DTS 任务查询的工具。下图中，使用查询设计器来生成一个查询，该查询联接 pubs 数据库中的三个表。

请点击此处，查看完整的图片。

图 8. 数据转换服务查询设计器界面

在转换任务中，还可以定义要对数据进行的更改。下表说明 DTS 提供的内置转换。

转换	说明
复制列	用于在不应用任何转换的情况下将数据直接从源列复制到目标列。
ActiveX 脚本	用于生成自定义转换。请注意，因为转换将逐行进行， ActiveX 脚本可能影响 DTS 软件包的执行速度。
DateTime 字符串	用于将源列中的日期或时间转换为目标列中的另一种格式。
小写字符串	用于将源列转换为小写字符形式，如需要，将其转换为目标数据类型。
大写字符串	用于将源列转换为全大写字符形式，如需要，将其转换为目标数据类型。
中间字符串	用于从源列抽取子字符串，对其进行转换后将结果复制到目标列。
修剪字符串	用于删除源列中字符串的前导空格、尾随空格和嵌入空格，然后将结果复制到目标列。
读取文件	用于打开文件内容（文件名称在源列中指定），然后将内容复制到目标列。
写入文件	用于将源列（数据列）的内容复制到一个文件中，该文件的路径由第二个源列（文件名列）指定。

还可以用编程方式创建自己的自定义转换。生成自定义转换最快速的方式是使用活动模板库 (ATL) 自定义转换模板， SQL Server 2000 DTS 样本程序中包括该模板。

数据泵错误记录

SQL Server 2000 中提供了一种记录转换错误的新方法。可以定义三种异常情况记录文件来记录软件包执行期间的情况：错误文本文件、源错误行文件和目标错误行文件。

· 一般错误信息将写入错误文本文件中。

· 如果转换失败，源行出现错误，该行将写入源错误行文件。

· 如果插入失败，目标行出现错误，该行将写入目标错误行文件。

在转换数据的任务中将定义异常情况日志文件。每个转换任务都有自己的日志文件。

数据泵阶段

默认情况下，数据泵有一个阶段，即行转换。该阶段是在未选择阶段的情况下，在转换数据任务、数据驱动查询任务和并行数据泵任务中映射列一级的转换时配置的。

多个数据泵阶段是 SQL Server 2000 中新增的。通过在 SQL Server 企业管理器中选择多阶段数据泵选项，在数据泵操作过程中的不同阶段都可以访问数据泵并添加功能。

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

请点击此处，查看完整的图片。

图 9. 数据泵进程

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

如果高级用户要向软件包添加功能，以便软件包能支持任何数据泵阶段，可以执行以下操作：

· 为每个要自定义的数据泵阶段编写一个 ActiveX 脚本阶段函数。如果使用 ActiveX 脚本函数来自定义数据泵阶段，不需要该软件包以外的其它任何代码。

· 使用 Microsoft Visual C++® 创建 COM 对象来自定义所选的数据泵阶段。在该软件包之外开发此程序，在执行转换过程中每个所选的阶段时将调用该程序。访问数据泵阶段的 ActiveX 脚本方法中每个所选阶段都使用不同的函数和输入点；而这种方法则不同，它在数据泵任务执行期间提供一个单一输入点，该输入点将被多个数据泵阶段调用。

保存 DTS软件包的选项

以下选项可用于保存 DTS 软件包：

· Microsoft SQL Server

如果要将软件包存储到网络中 SQL Server 的任意实例上，该选项将 DTS 软件包保存到 Microsoft SQL Server ，保留一个便于使用的这些软件包的清单，并在软件包开发进程中添加和删除软件包版本。

· SQL Server 2000 元数据服务

如果准备跟踪软件包版本、元数据和数据系列信息，该选项将 DTS 软件包保存到元数据服务。

· 结构化存储文件

如果希望在网络间复制、移动和发送软件包而不必将其存储在 Microsoft SQL Server 数据库中，该选项将 DTS 软件包保存到结构化存储文件。

· Microsoft Visual Basic

如果要将 DTS 软件包合并到 Visual Basic 程序中或将其用作 DTS 应用程序开发的原型，该选项将通过 DTS 设计器或 DTS 导入 / 导出向导所创建的 DTS 软件包保存到 Microsoft Visual Basic 文件。

DTS 作为应用程序开发平台

DTS 设计器为数据移动任务提供了多种解决方案。因为提供通过编程方式访问 DTS 对象模型的功能， DTS 扩展了可用的解决方案的数目。使用 Microsoft Visual Basic 、 Microsoft Visual C++ 或任何其它支持 COM 的应用程序开发系统，都可以使用图形化工具所不支持的功能开发出适合于您的环境的自定义 DTS 解决方案。

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

· 生成软件包

无需使用 DTS 设计器或 DTS 导入 / 导出向导，您就可以开发极为复杂的软件包，并可访问对象模型中的全套功能。

· 扩展软件包

通过构建自定义任务和转换，可以添加一些适用于您的业务并可在 DTS 内重复使用的新功能。

· 执行程序包

并非一定要使用所提供的工具来执行 DTS 软件包，可以用编程方式执行 DTS 软件包并通过 COM 事件显示进度，并允许构建嵌入的或自定义的 DTS 执行环境。

样本 DTS 程序有助于了解 DTS 编程的入门知识。该样本可与 SQL Server 2000 一起安装。

如果开发 DTS 应用程序，可以重新分发 DTS 文件。有关详细信息，请参阅 SQL Server 2000 光盘上的 Redist.txt 。

有关详细信息

Microsoft SQL Server 2000 联机图书包含索引视图的详细信息。有关其它信息，请参阅以下资源：

· sql">Microsoft SQL Server Web 站点（中文）。

· sqlserver">Microsoft SQL Server 开发人员中心（英文）。

· sqlmag.com/">SQL Server 杂志（英文）。

· Microsoft.public.sqlserver.server 和 microsoft.public.sqlserver.datawarehouse 新闻组，其站点是： asp?icp=chinacommunity&slcid=cn&MSCOMTB=ICP_ 中文新闻组 ">http://communities.microsoft.com/newsgroups/default.asp?icp=chinacommunity&slcid=cn&MSCOMTB=ICP_ 中文新闻组（中文）。

· 关于 SQL Server 的 Microsoft 正式课程。有关最新的课程信息，请参阅 Microsoft 培训和服务站点（英文）。