Microsoft SQL Server 的数据转换服务.docx

Microsoft SQL Server 的数据转换服务.docx

《Microsoft SQL Server 的数据转换服务.docx》由会员分享，可在线阅读，更多相关《Microsoft SQL Server 的数据转换服务.docx（13页珍藏版）》请在冰豆网上搜索。

MicrosoftSQLServer的数据转换服务

MicrosoftSQLServer2000的数据转换服务

DTS简介

大多数组织都使用多种格式和多个位置来存储数据。

为了支持决策、改善系统性能或对现有系统进行升级，经常必须将数据从一个数据存储位置移动到另一个存储位置。

Microsoft®SQLServer™2000数据转换服务（DTS）提供一组工具，可以从不同的源将数据抽取、转换和合并到一个或多个目标位置。

借助于DTS工具，您可以创建适合于您的组织特定需要的自定义移动解决方案，如以下方案所示：

∙您已在较早版本的SQLServer或另一平台上部署了一个数据库应用程序，如MicrosoftAccess。

该应用程序的新版本要求SQLServer2000，因此需要您更改数据库架构并转换某些数据类型。

要复制和转换数据，您可以生成一个DTS解决方案，该方案可把数据库对象从原数据源复制到SQLServer2000数据库中，同时重新映射列并更改数据类型。

可以使用DTS工具运行此解决方案，或者将该解决方案嵌入您的应用程序。

∙您必须将几个主要的MicrosoftExcel电子表格合并到SQLServer数据库。

几个部门会在月末创建这些电子表格，但是并没有设定完成所有电子表格的时间。

要合并电子表格，可以生成一个DTS解决方案，在消息传送到消息队列时运行该解决方案。

此消息触发DTS从电子表格抽取数据，执行所有定义的转换然后将数据加载到SQLServer数据库中。

∙您的数据仓库包含有关业务操作的历史数据，您使用MicrosoftSQLServer2000分析服务来汇总数据。

而该数据仓库每晚需要从联机事务处理（OLTP）数据库进行更新。

OLTP系统每天24小时都在运行，所以性能至关重要。

您可以生成一个DTS解决方案，该方案使用文件传输协议（FTP）将数据文件移动到本地驱动器，将数据加载到一个实际的表中，然后使用分析服务对数据进行合计。

可以安排每晚运行DTS解决方案，还可以使用新的DTS记录选项来跟踪该进程的进行时间，从而可以分析不同时间段内的性能。

什么是DTS？

DTS是一组工具，用于在一个或多个数据源（如MicrosoftSQLServer、MicrosoftExcel或MicrosoftAccess）间导入、导出和转换各种数据。

通过OLEDB（一种数据访问的开放式标准）提供连接,通过用于ODBC的OLEDB提供程序来支持ODBC（开放式数据库连接）数据源。

可以将DTS解决方案创建为一个或多个软件包。

每个软件包可包含一组已安排好的任务，它们定义要执行的工作、对数据和对象的转换、定义任务执行的工作流约束条件以及数据源和目标间的连接方式。

DTS软件包还提供一些服务，如记录软件包执行详细情况、控制事务和处理全局变量。

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

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

∙DTS设计器以图形方式来实现DTS对象模型，可用于创建具有一系列功能的DTS软件包。

∙DTSRun是用于执行现有DTS软件包的命令提示实用程序。

∙DTSRunUI是DTSRun的图形界面，可以传递全局变量以及生成命令行。

∙SQLAgent不是DTS应用程序，DTS将其用于安排软件包的执行。

使用DTS对象模型还可以用编程方式创建和运行软件包，生成自定义任务和自定义转换。

DTS的新功能

MicrosoftSQLServer2000中加入了多种DTS增强功能和新功能：

∙新的DTS任务包括FTP任务、执行软件包任务、动态属性任务和信息队列任务。

∙增强的记录功能可以保存每个软件包的执行信息，从而为您保留一个完整的执行历史记录，并可以查看每一任务的每个进程的信息。

可以生成异常情况文件，文件中包含因错误而无法处理的数据行。

∙您可以将DTS软件包另存为MicrosoftVisualBasic®文件。

∙新的多阶段数据泵允许高级用户自定义不同阶段的数据转换操作。

还可以将全局变量用作查询的输入参数。

∙您可以在DTS转换任务和执行SQL任务时使用参数化源查询。

∙可以使用执行软件包任务将全局变量的值从父软件包动态分配到子软件包。

使用DTS设计器

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

您可以使用DTS设计器执行以下任务：

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

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

∙编辑现有软件包。

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

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

图1.DTS设计器界面

通过将连接和任务拖动到设计工作表，然后指定工作流的执行顺序，可以方便地使用DTS设计器来生成功能强大的DTS软件包。

以下各部分将对任务、工作流、连接和转换进行定义，并阐释使用DTS设计器实施DTS解决方案的方便性。

任务：

定义软件包中的步骤

一个DTS软件包通常包括一个或多个任务。

每个任务定义一个在软件包执行期间可能要执行的工作项。

可以使用这些任务进行以下操作：

∙转换数据

转换数据任务

用于在源和目标间移动数据，可以选择对数据应用列一级的转换。

数据驱动的查询任务

用于对数据执行灵活的基于SQL事务的操作，包括存储过程和INSERT、UPDATE或DELETE语句。

并行数据泵任务1

只可按编程方式使用，并行数据泵任务执行的功能与转换数据和数据驱动的查询任务相同，但它支持OLEDB2.5或更高版本所定义的分层次的行集合。

∙

∙复制和管理数据

批量插入任务

用于快速将大量数据加载到SQLServer表或视图中。

sql.gif"border="0"/>

执行SQL任务

用于在软件包执行期间运行SQL语句。

执行SQL任务还可以保存作为查询结果的数据。

复制SQLServer对象任务

用于将SQLServer对象从一个SQLServer或其实例复制到另一个SOLServer或实例。

可以复制对象（如：

数据和表）以及对象（如：

视图和存储过程）的定义。

传输数据库任务1

用于将SQLServer数据库从SQLServer7.0版本或SQLServer2000的一个实例移动或复制到SQLServer2000的一个实例中。

传输错误消息任务1

用于将针对用户的错误消息（通过sp_addmessage系统存储过程创建）从SQLServer7.0或SQLServer2000的一个实例复制到SQLServer2000的一个实例中。

传输登录任务1

用于将登录信息从SQLServer7.0或SQLServer2000的一个实例复制到SQLServer2000的一个实例中。

传输作业任务1

用于将作业从SQLServer7.0或SQLServer2000的一个实例复制到SQLServer2000的一个实例中。

传输主存储过程任务1

用于将存储过程从SQLServer7.0或SQLServer2000的一个实例的主数据库复制到SQLServer2000的一个实例的主数据库。

∙

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

ActiveX脚本任务

用于编写代码以执行在其它DTS任务中无法执行的功能。

动态属性任务1

用于在软件包运行时从DTS软件包以外的源检索值，并将这些值分配给选定的软件包属性。

执行软件包任务1

用于运行软件包内其它的DTS软件包。

执行进程任务

用于运行可执行程序或批处理文件。

文件传输协议（FTP）任务1

用于从远程服务器或Internet下载数据文件。

消息队列任务1

用于从Microsoft消息队列发送和接收消息。

发送邮件任务

用于发送电子邮件消息。

分析服务处理任务2

用于对SQLServer2000分析服务中定义的一个或多个对象执行处理。

数据挖掘任务1,2

用于从SQLServer2000分析服务中定义的数据挖掘模式对象中创建谓词查询或输出表。

1SQLServer2000中的新功能。

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

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

为说明如何使用这些任务，这里显示一个简单的DTS软件包，其中包括两项任务：

MicrosoftActiveX®脚本任务以及发送邮件任务：

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

ActiveX脚本任务可以包含任何ActiveX脚本引擎，包括MicrosoftVisualBasicScriptingEdition（VBScript）、MicrosoftJScript®或ActiveStateActivePerl。

这些搜索引擎可以从下载。

发送邮件任务可以发送消息，指示软件包已运行。

请注意，这些任务尚未排序。

执行软件包时，ActiveX脚本任务和发送邮件任务将同时运行。

工作流：

设置任务优先级

定义任务组时，通常有一个执行任务的顺序。

如果任务已排序，每个任务将成为进程中的一个步骤。

在DTS设计器中，在DTS设计器工作表上操作任务，并使用优先级约束条件来控制执行任务的顺序。

优先级约束条件继而将软件包中的任务链接起来。

下表显示在DTS中可以使用的优先级约束条件类型。

优先级约束条件

说明

按完成顺序

（蓝色箭头）

如果希望在任务1完成前不执行任务2，而不考虑执行结果如何，则应使用“按完成顺序”优先级约束条件链接任务1和任务2。

按成功情况

（绿色箭头）

如果希望在任务1成功完成前不执行任务2，应使用“按成功情况”优先级约束条件链接任务1和任务2。

按失败情况

（红色箭头）

如果希望只在任务1无法成功执行的情况下才执行任务2，应使用“按失败情况”优先级约束条件链接任务1和任务2。

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

当ActiveX脚本任务完成后，无论成功或失败，都将运行发送邮件任务。

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

可以分别配置发送邮件任务，一个配置为使用“按成功情况”约束条件，一个配置为使用“按失败情况”约束条件。

两个发送邮件任务可根据ActiveX脚本成功还是失败发送不同的消息。

图4.邮件任务

您还可以对一个任务采用多个优先级约束条件。

例如，发送邮件任务“管理通知”可以在脚本#1采用“按成功情况”约束条件，在脚本#2采用“按失败情况”约束条件。

在这些情况下，DTS假设存在逻辑关系“AND”。

因此，只有在脚本#1成功执行而脚本#2失败的情况下，才会发送“管理通知”消息。

图5.示例：

对一项任务采用多个优先级约束条件

连接：

访问和移动数据

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

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

可用的连接类型包括：

∙Microsoft数据访问组件（MDAC）驱动程序

sql.gif"border="0"/>

用于SQLServer的MicrosoftOLEDB提供程序

Microsoft数据链接

用于Oracle的MicrosoftODBC驱动程序

∙

∙MicrosoftJet驱动程序

dBase5

MicrosoftAccess

HTML文件（源）

MicrosoftExcel97-2000

Paradox5.X

∙

∙其它驱动程序

文本文件（源）

文本文件（目标）

其它连接

∙

DTS允许您使用任何OLEDB连接。

使用“连接”工具栏上的图标可以便捷地访问常用的各种连接。

下图显示一个使用两种连接的软件包。

数据将从Access数据库（源连接）复制到SQLServer产品数据库（目标连接）。

图6.示例：

使用两种连接的软件包

此软件包中的第一个步骤是一个执行SQL任务，该任务检查是否目标表已经存在。

如果存在，将删除它然后重新创建。

成功完成执行SQL任务后，第二个步骤是将数据复制到SQLServer数据库。

如果复制操作失败，第三个步骤是发送一份电子邮件。

数据泵：

转换数据

DTS数据泵是一个DTS对象，它驱动数据的导入、导出和转换操作。

在执行数据转换、数据驱动查询和并行数据泵任务期间将使用数据泵。

执行这些任务的过程是：

在源和目标连接上创建行集合，然后创建数据泵实例在源和目标间移动这些行。

在复制每一行时，将进行转换操作。

下图中，在第二个步骤中，转换数据任务在AccessDB任务和SQLProductionDB任务之间进行。

转换数据任务用连接间的灰色箭头表示。

图7.示例：

转换数据任务

要定义从源连接收集的数据，可以生成一个转换任务的查询。

DTS支持参数化查询，参数化查询允许在执行查询时定义查询值。

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

下图中，使用查询设计器来生成一个查询，该查询联接pubs数据库中的三个表。

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

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

在转换任务中，还可以定义要对数据进行的更改。

下表说明DTS提供的内置转换。

转换

说明

复制列

用于在不应用任何转换的情况下将数据直接从源列复制到目标列。

ActiveX脚本

用于生成自定义转换。

请注意，因为转换将逐行进行，ActiveX脚本可能影响DTS软件包的执行速度。

DateTime字符串

用于将源列中的日期或时间转换为目标列中的另一种格式。

小写字符串

用于将源列转换为小写字符形式，如需要，将其转换为目标数据类型。

大写字符串

用于将源列转换为全大写字符形式，如需要，将其转换为目标数据类型。

中间字符串

用于从源列抽取子字符串，对其进行转换后将结果复制到目标列。

修剪字符串

用于删除源列中字符串的前导空格、尾随空格和嵌入空格，然后将结果复制到目标列。

读取文件

用于打开文件内容（文件名称在源列中指定），然后将内容复制到目标列。

写入文件

用于将源列（数据列）的内容复制到一个文件中，该文件的路径由第二个源列（文件名列）指定。

还可以用编程方式创建自己的自定义转换。

生成自定义转换最快速的方式是使用活动模板库（ATL）自定义转换模板，SQLServer2000DTS样本程序中包括该模板。

数据泵错误记录

SQLServer2000中提供了一种记录转换错误的新方法。

可以定义三种异常情况记录文件来记录软件包执行期间的情况：

错误文本文件、源错误行文件和目标错误行文件。

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

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

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

在转换数据的任务中将定义异常情况日志文件。

每个转换任务都有自己的日志文件。

数据泵阶段

默认情况下，数据泵有一个阶段，即行转换。

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

多个数据泵阶段是SQLServer2000中新增的。

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

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

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

图9.数据泵进程

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

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

∙为每个要自定义的数据泵阶段编写一个ActiveX脚本阶段函数。

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

∙使用MicrosoftVisualC++®创建COM对象来自定义所选的数据泵阶段。

在该软件包之外开发此程序，在执行转换过程中每个所选的阶段时将调用该程序。

访问数据泵阶段的ActiveX脚本方法中每个所选阶段都使用不同的函数和输入点；而这种方法则不同，它在数据泵任务执行期间提供一个单一输入点，该输入点将被多个数据泵阶段调用。

保存DTS软件包的选项

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

∙MicrosoftSQLServer

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

∙SQLServer2000元数据服务

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

∙结构化存储文件

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

∙MicrosoftVisualBasic

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

DTS作为应用程序开发平台

DTS设计器为数据移动任务提供了多种解决方案。

因为提供通过编程方式访问DTS对象模型的功能，DTS扩展了可用的解决方案的数目。

使用MicrosoftVisualBasic、MicrosoftVisualC++或任何其它支持COM的应用程序开发系统，都可以使用图形化工具所不支持的功能开发出适合于您的环境的自定义DTS解决方案。

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

∙生成软件包

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

∙扩展软件包

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

∙执行程序包

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

样本DTS程序有助于了解DTS编程的入门知识。

该样本可与SQLServer2000一起安装。

如果开发DTS应用程序，可以重新分发DTS文件。

有关详细信息，请参阅SQLServer2000光盘上的Redist.txt。

有关详细信息

MicrosoftSQLServer2000联机图书包含索引视图的详细信息。

有关其它信息，请参阅以下资源：

∙sql">MicrosoftSQLServerWeb站点（中文）。

∙sqlserver">MicrosoftSQLServer开发人员中心（英文）。

∙Server杂志（英文）。

∙Microsoft.public.sqlserver.server和microsoft.public.sqlserver.datawarehouse新闻组，其站点是：

asp?

icp=chinacommunity&slcid=cn&MSCOMTB=ICP_中文新闻组">

∙关于SQLServer的Microsoft正式课程。

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