第 14 章数据转换服务

第14章数据转换服务 • 14．1关于DTS • 14．2 导入及导出数据 • 14．3 DTS设计器 • 14.4本章总结 • 14.5思考与实训

学习目标： 通过本章学习，你能够学会： • 了解SQL Server数据分布的基本概念 • 运用DTS管理器导入导出数据 • 学会DTS设计器的使用

在使用SQL Server 的过程中由于经常需要从多个不同地点将数据集中起来或向多个地点复制数据,所以数据的导出,导入是极为常见的操作.我们可以使用BCP 命令来完成这一任务,但是记住 BCP 的命令格式是一件令人头痛的苦差事，虽然你可以查看帮助文件，所以我们需要功能强大操作简单的工具来完成这一任务，数据转换服务DTS 提供了这种支持。因此本章将介绍DTS 的基本情况、讨论DTS 的各个构建组件以及如何完成数据转换服务。

14．1关于DTS • 为了支持企业决策，许多组织都需将数据集中起来进行分析。但是通常数据总是以不同的格式存储在不同的地方。有的可能是文本文件，有的虽然具有表结构但不属于同一种数据源，这些情况极大地妨碍数据的集中处理。SQL Server 为我们提供了令人欣慰的组件，即Data Transformation Services（ DTS）。 DTS 本身包含多个工具并提供了接口来实现在任何支持OLE DB 的数据源间导入、导出或传递数据，并使这一工作变得简单高效。这意味着不仅可以在SQL Server 数据源间进行数据的转储，而且可以把Sybase， Oracle， Informix 下的数据传递到SQL Server。

利用Data Transformation Services （DTS） 可能在任何OLE DB、 ODBC 驱动程序的数据源或文本文和SQL Server 之间导入、导出或传递数据。具体表现在： • (1) 数据的导入、导出服务 • 通过读写数据在不同应用程序间交换数据。例如，可将文本文件或Microsoft Access 数据库导入到SQL Server，也可以把数据从SQL Server 导出到任何OLE DB 数据源。 • (2) 转换数据 • 所谓传递是指在数据未到达目标数据源前而对数据采取的系列操作。比如DTS 允许从源数据源的一列或多列计算出新的列值，然后将其存储在目标数据库中。

(3) 传递数据库对象 • 在异构数据源情况下DTS，的内置工具只能在数据源间传递表定义和数据。如果要传递其它数据库对象，如索引、约束、视图时，必须定义一个任务，从而在目标数据库上执行那些包含在任务中的SQL 语句，SQL 语句是被用来创建这些数据库对象的。

DTS 结构如下: • DTS 将数据导入、导出或传递定义成可存储的对象，即包裹或称为包。每一个包都是包括一个或具有一定顺序的多个任务的工作流。每个任务可以将数据从某一数据源拷贝至目标数据源或使用Active 脚本转换数据或执行SQL 语句或运行外部程序。也可以在SQL Server 数据源间传递数据库对象。

包对象用来创建并存储步骤，这些步骤定义了一系列任务执行的顺序以及执行任务的必要细节。包对象中还包括源列、目标列以及有关在数据传递过程中如何操纵数据的信息。 • 包可以存储在DTS COM 结构的存储文件中、msdb 数据库中、或Microsoft Repository 中。 • 可以通过以下工具来运行包，它们是：dtsrun 工具，DTS Designer， DTS 的导入、导出向导，SQL Server Agent 来运行规划作业，使用Execute 方法调用DTS 包对象的COM应用程序。

包是顶层对象，它包含三种底层对象：连接、任务、步骤。 • 1 、连接 • 连接定义了有关源和目标数据（数据源或文件）的信息这些信息包括数据格式和位置，以及安全认证的密码。DTS 包可不包含或包含多个连接。使用连接的任务有： • DTS Data Pump 任务 • 执行SQL 任务 • 数据驱动查询任务 • 定制任务

有三种类型的连接对象 • (1)数据源连接 • 数据源连接定义了有关源和目标OLE DB 数据源的信息。这些信息包括服务器名称、数据格式和位置，以及安全认证的密码。第一个使用连接的任务负责创建该连接。如果使用ODBC 的OLE DB 提供者，则连接也可以定义ODBC 数据源信息。 • (2)文件连接 • 文件连接定义了有关源和目标文件的信息。这些信息包括文件格式和位置。 • (3)Microsoft 数据连接对象 • Microsoft 数据连接对象或者加载数据连接文件（*.udl）或者为OLE DB 提供者设置数据连接文件的属性。

2、任务 • 每个DTS 包都含有一个或多个任务，每个任务都是数据转换（传递）处理的工作项目。任务的种类包括： • 执行SQL 任务：即执行SQL 语句； • Data Pump 任务该任务：为Data Pump 操作定义了源和目标数据源以及数据转换。Data Pump 从源和目标OLE DB 数据源间拷贝并转换数据; • ActiveX 脚本执行：ActiveX, VB, Jscript 或Perscript 脚本。凡是脚本支持的操作都可以执行； • 执行处理任务（Execute Process task）：指执行外部程序； • 批量插入指执行：SQL Server 批拷贝操作； • 发送邮件：使用SQL Mail 发送寻呼或邮件； • 数据驱动查询：执行OLE DB 数据源间的高级数据传递； • 转换SQL Server 对象：即从SQL Server OLE DB 数据源向另外的同类数据源复制对象，例如表、索引、视图。

3、步骤 • 步骤对象定义了任务执行的顺序，以及某一任务的执行是否依赖于前一个任务的结果。如果某一任务不与步骤对象相关联，则其将无法被执行。可以为某一步骤设定运行条件，使其只在一定条件才被执行，为了提高执行的性能，也可以并行执行多个步骤。 • 步骤的一个重要特性是步骤优先权约束。步骤优先权约束定义了前一步必须满足哪些条件之后才会执行当前步骤，通过步骤优先权约束可以控制任务的执行顺序。有三种类型的优先权约束： • (1)完成：表示前一步骤完成后就执行当前步骤，而不管其成功与否； • (2)成功：表示前一步骤只有成功执行才执行当前步骤； • (3)失败：表示前一步骤执行失败时才执行当前步骤。

14．2 导入及导出数据 • DTS 导入、导出向导帮助用户交互式地建立包，从而在具有OLE DB 和ODBC 驱动程序的源和目标数据源间进行数据的导入、导出和转换。下面将以两个具体例子来讲解如何使用DTS 向导，同时将对其中涉及的每一个选项进行详细的介绍。

[实例14.1]使用DTS 向导导出pubs 数据库中的authors 表。

其中各选项的含义如下： • Create destination table：在从源表拷贝数据前首先创建目标表，在缺省情况下总是假设目标表不存在，如果存在则发生错误、除非选中了Drop and recreate destination table if it exists 选项； • Delete rows in destination table：在从源表拷贝数据前将目标表的所有行删除，仍保留目标表上的约束和索引，当然使用该选项的前提是目标表必须存在； • Append rows to destination table：把所有源表数据添加到目标表中，目标表中的数据、索引、约束仍保留。但是数据不一定追加到目标表的表尾，如果目标表上有聚簇索引，则可以决定将数据插入何处； • Drop and recreate destination table：如果目标表存在，则在从源表传递来数据前将目标表、表中的所有数据、索引等删除后重新创建新目标表； • Enable identity insert：允许向表的标识列中插入新值。

在进行数据转换时，可以通过脚本语言（如Jscript Perscript Vbscript）对源表中的某一列施加某种运算（乘、除或将该分割成几列、或将几列合并成一列），然后再将这种结果复制到目标表。

在When 选项区可以选择与包有关的操作： • Run immediately：表示立即运行包； • Create DTS package for Replication：表示让由发布目标来进行复制； • Schedule DTS package for later execution：表示将包保存之后，在以后的某一规划时间运行。 • 在Save 选项，选中Save DTS Package，则将包进行保存。 • SQL Server：将包存储在msdb 数据库中； • SQL Server Meta Data Service ：将包存储在Repository 中； • Structured Storage File：以DTS COM 结构的文件格式存储。容易通过文件服务器进行邮递和分发。 • Visual Basic File。

例14-2：在该例中，利用DTS Wizard向数据库中导入一个文本文件。其创建的步骤与例14。1基本相同，下面主要介绍不同的创建步骤： • (1) 建立源连接:

(2) 设定文件格式

（3）确定分界符:

（4）定义每个字段长度。 • （5）为导入数据输入目标表。 • （6）定义数据转换要求。 • （7）执行数据导入。

14．3 DTS设计器 • 可以使用 DTS 设计器执行以下任务： • 1.创建包含一个或多个步骤的简单软件包。 • 2.创建包括复杂工作流的软件包，这些工作流包括多个步骤，而这些步骤使用条件逻辑、事件驱动代码或与数据源的多种连接。 • 3.编辑现有软件包。

DTS 设计器界面包括一个工作区（用于生成软件包）、几个工具栏（其中包含可拖动到设计工作表上的软件包元素）以及几个菜单（其中包含工作流和软件包管理命令）。 • 通过将连接和任务拖动到设计工作表，然后指定工作流的执行顺序，可以方便地使用 DTS 设计器来生成功能强大的 DTS 软件包。以下各部分将对任务、工作流、连接和转换进行定义，并阐释使用 DTS 设计器实施 DTS 解决方案的方便性。

定义软件包中的步骤: 一个 DTS 软件包通常包括一个或多个任务。每个任务定义一个在软件包执行期间可能要执行的工作项。可以使用这些任务进行以下操作：（1）转换数据: • 转换数据任务：图标为，用于在源和目标间移动数据，可以选择对数据应用列一级的转换。 • 数据驱动的查询任务：图标为，用于对数据执行灵活的基于 SQL 事务的操作，包括存储过程和 INSERT、UPDATE 或 DELETE 语句。 • 并行数据泵任务：只可按编程方式使用，并行数据泵任务执行的功能与转换数据和数据驱动的查询任务相同，但它支持 OLE DB 2.5 或更高版本所定义的分层次的行集合。

（2）复制和管理数据: • 批量插入任务：图标为，用于快速将大量数据加载到 SQL Server 表或视图中。 • 执行 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 的一个实例的主数据库。

(3) 将任务作为软件包中的作业运行

14.4本章总结 • 本章主要介绍了SQL Server提供的一个重要功能之一就是数据传输服务。阐明了如何使用DTS向导在不同的数据库之间进行数据的传输，以及传输过程中应该注意的一些常见问题。还介绍了SQL Server提供的DTS包设计工具的基本概念，及其使用方法。通过本章的学习，用户应该对SQL Server的数据传输服务有一个基本的了解，能够完成一定复杂程度的数据传输工作。

14.5思考与实训 • 14.5.1思考题 1．DTS功能有哪些？ 2．包的概念，包含哪三种底层对象。 3．如何进行数据的导入和导出。 4．使用DTS设计器可以执行哪些任务？ 5．DTS软件包执行的操作有哪些？

14.5.2实训 • 1、实训目的 • （1）掌握SQL Server 2000中使用DTS进行数据导入导出的方法。 • （2）掌握DTS设计器使用的方法。 • 2、实训环境 • SQL Server 2000运行环境。 • 3、实训内容 • （1）下面是一个存放在Excel表中的信息，利用导入导出向导将其导入到学生库中的学生成绩表中。

（2）利用数据导出向导讲pubs数据库中的authors表和原来创建在authors表上的触发器和存储过程转换到学生库中。（2）利用数据导出向导讲pubs数据库中的authors表和原来创建在authors表上的触发器和存储过程转换到学生库中。 4. 实训要求（1）将实训3的内容在SQL Server 2000环境中调试成功，并将编写的程序调试、运行正确成功后，存入磁盘中。（2）每次实训后，总结一下实训的重点内容和应注意的问题，并写成实训报告的形式上交。

第 14 章 数据转换服务