DTS
返回总目录↵ ↵ ↵
有关数据转换服务的基本概念如何使用DTS向导DTS Designer
在使用SQL Server的过程中由于经常需要从多个不同地点将数据集中起来或向多个地点复制数据所以数据的导出导入是极为常见的操作我们可以使用BCP命令来完成这一任务但是记住BCP的命令格式是一件令人头痛的苦差事虽然你可以查看帮助文件所以我们需要功能强大操作简单的工具来完成这一任务数据转换服务DTS提供了这种支持因此本章将介绍DTS的基本情况讨论DTS的各个构建组件以及如何完成数据转换服务
18.1 数据转换服务基本概念
18.1.1 数据转换服务简介
为了支持企业决策
许多组织都需将数据集中起来进行分析
但是
通常数据总是
以不同的格式存储在不同的地方有的可能是文本文件有的虽然具有表结构但不属于同一种数据源这些情况极大地妨碍数据的集中处理SQL Server为我们提供了令人
DTS本身包含多个工具并提供欣慰的组件即Data Transformation ServicesDTS
了接口来实现在任何支持OLE DB的数据源间导入导出或传递数据并使这一工作变得简单高效这意味着不仅可以在SQL Server数据源间进行数据的转储而且可以把Sybase
Oracle
Informix下的数据传递到SQL Server
利用Data Transformation ServicesDTS可能在任何OLE DBODBC驱动程序的数据源或文本文和SQL Server之间导入导出或传递数据具体表现在
数据的导入导出服务
通过读写数据在不同应用程序间交换数据例如可将文本文件或Microsoft Access数据库导入到SQL Server也可以把数据从SQL Server导出到任何OLE DB数据源
转换数据
所谓传递是指在数据未到达目标数据源前而对数据采取的系列操作比如DTS允许从源数据源的一列或多列计算出新的列值然后将其存储在目标数据库中
传递数据库对象在异构数据源情况下
DTS的内置工具只能在数据源间传递表定义和数据
如果
要传递其它数据库对象如索引约束视图时必须定义一个任务从而在目标数据库上执行那些包含在任务中的SQL语句SQL语句是被用来创建这些数据库对象的
18.1.2 DTS结构
DTS将数据导入导出或传递定义成可存储的对象即包裹或称为包每一个包都是包括一个或具有一定顺序的多个任务的工作流每个任务可以将数据从某一数据源拷贝至目标数据源或使用Active脚本转换数据或执行SQL语句或运行外部程序也可以在SQL Server数据源间传递数据库对象
包对象用来创建并存储步骤这些步骤定义了一系列任务执行的顺序以及执行任务的必要细节包对象中还包括源列目标列以及有关在数据传递过程中如何操纵数据的信息
包可以存储在DTS COM结构的存储文件中msdb数据库中或Microsoft Repository中
可以通过以下工具来运行包它们是dtsrun工具DTS DesignerDTS的导入导出向导SQL Server Agent来运行规划作业使用Execute方法调用DTS包对象的COM
应用程序
包是顶层对象
它包含三种底层对象
连接
任务
步骤
1连接
连接定义了有关源和目标数据数据源或文件的信息这些信息包括数据格式和位置以及安全认证的密码DTS包可不包含或包含多个连接使用连接的任务有
DTS Data Pump任务 执行SQL任务 数据驱动查询任务 定制任务
有三种类型的连接对象
数据源连接
数据源连接定义了有关源和目标OLE DB数据源的信息
这些信息包括服务器名
称数据格式和位置以及安全认证的密码第一个使用连接的任务负责创建该连接如果使用ODBC的OLE DB提供者则连接也可以定义ODBC数据源信息
文件连接
文件连接定义了有关源和目标文件的信息这些信息包括文件格式和位置 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
果
步骤
步骤对象定义了任务执行的顺序
以及某一任务的执行是否依赖于前一个任务的结则其将无法被执行
可以为某一步骤设定运行
如果某一任务不与步骤对象相关联
条件使其只在一定条件才被执行为了提高执行的性能也可以并行执行多个步骤
有三种
步骤的一个重要特性是步骤优先权约束些条件之后才会执行当前步骤类型的优先权约束
完成表示前一步骤完成后
成功 失败
步骤优先权约束定义了前一步必须满足哪
通过步骤优先权约束可以控制任务的执行顺序
就执行当前步骤
而不管其成功与否
表示前一步骤只有成功执行才执行当前步骤表示前一步骤执行失败时才执行当前步骤
某一步骤可有多个优先权约束只有前一步满足所有的约束后才能执行当前步骤
18.2 DTS导入导出向导
DTS导入
导出向导帮助用户交互式地建立包
从而在具有OLE DB和ODBC驱
动程序的源和目标数据源间进行数据的导入导出和转换下面将以两个具体例子来讲解如何使用DTS向导同时将对其中涉及的每一个选项进行详细的介绍
例18-1使用DTS向导导出pubs数据库中的authors表利用SQL Server Enterprise Manager执行数据导出的步骤为
1
启动SQL Server Enterprise Manager后登录到指定的服务器右击DataTransformation Services文件夹在弹出菜单中选择All tasksExport data打开DTS Export Wizard 如图18-1所示图18-1 DTS Import/Export Wizard对话框
2
单击下一步按钮打开Choose a Data Source对话框如图18-2所示首先在Source Server旁的下拉列表中选择数据源在本例中选择MicrosoftOLE DB Provider for SQL Server如果使用SQL Server认证方式则应输入访问数据库的合法用户账号和密码在Database 旁的下拉列表中选择pubs先单击Refresh按钮图18-2 Choose a Data Source对话框
3
单击下一步按钮打开Choose a Destination对话框如图18-3所示在Destination旁的下拉列表中选择Microsoft OLE DB Provider for SQLServerDatabase 旁的下拉列表中选择DBA_pubs数据库图18-3 Choose a Destination对话框
4
单击下一步按钮打开Specify Table Copy or Query对话框如图18-4所示在此处可以指定传递的内容可以传递表或某一查询的数据结果集甚至于数据库对象在本例中选择Copy tablesfrom the source database
图18-4 Specify Table Copy or Query对话框
5
单击下一步打开Select Source Table and View对话框如图18-5所示从中选择一个或多个表或视图进行传递通过Preview按钮可对将要传递的数据进行预览图18-5 Select Source Table and View对话框
6
如果想定义数据转换时源表和目标表之间列的对应关系则单击Transform列的方格按钮打开Columns MappingTransformationand Constrains对话框如图18-6所示Column Mappings标签页图18-6 Column Mappings, Transformations and Constraints对话框 –
其中各选项的含义如下
Create destination table
总是假设目标表不存在
destination table if it exists选项
在从源表拷贝数据前首先创建目标表在缺省情况下如果存在则发生错误除非选中了Drop and recreate
Delete rows in destination table在从源表拷贝数据前将目标表的所有行删除
仍保留目标表上的约束和索引当然使用该选项的前提是目标表必须存在
Append rows to destination table把所有源表数据添加到目标表中目标表中
的数据
索引
约束仍保留
但是数据不一定追加到目标表的表尾
如果目标
表上有聚簇索引则可以决定将数据插入何处
Drop and recreate destination table如果目标表存在
则在从源表传递来数据前
将目标表表中的所有数据索引等删除后重新创建新目标表 Enable identity insert允许向表的标识列中插入新值
7在进行数据转换时可以通过脚本语言如Jscript Perscript
Vbscript
对源表中的某一列施加某种运算乘除或将该分割成几列或将几列合并然后再将这种结果复制到目标表此时应选中Columns Mapping成一列Transformationand Constrains对话框的 Transformations标签页如图18-7所示图18-7 Column Mappings, Transformations, and Constraints 对话框– Transformations标签页
其中各选项的含义如下
Language表示使用哪种脚本语言
Copy the source columns directly to the destination column表示不对原表进行任
何处理
Transform information as it is copied to the destination表示通过脚本语言编写
如何改变原始例的程序比如我们假设源表在拷贝到目标表时将源表address
列全转为小写则在空白区域的VB脚本是
'**********************************************************************
' Visual Basic Transformation Script' Copy each source column to the' destination column
'***********************************************************************Function MainDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationEnd Function
\"au_id\"
= DTSSource
\"au_id\"
\"au_fname\"\"au_lname\"\"phone\"
\"address\"
\"city\"\"state\"\"zip\"
\"contract\"
\"au_fname\"\"au_lname\"\"phone\"\"address\"\"city\"\"state\"\"zip\"
= DTSSource= DTSSource = lcase
= DTSSource = DTSSource = DTSSource
DTSSource
= DTSSource
\"contract\" = DTSSource
Main = DTSTransformStat_OK
8
应选中Columns MappingTransformationConstraints 标签页如图18-8所示and Constraints 对话框的图18-8 Columns Mapping, Transformation, and Constraints 对话框– Constraints标签页
9返回Select Source Table对话框单击下一步Replicate Package对话框如图18-9所示打开SaveSchedule and
图18-9 SaveSchedule and Replicate Package对话框
在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
务器进行邮递和分发 Visual Basic File10
以DTS COM结构的文件格式存储
容易通过文件服
单击下一步打开Save DTS Package对话框如图18-10所示在Name输入该包的名称AuthorsPackage可以将包保存在本地服务器或其它的远程服务器也可以选择适当的认证方式如果选择SQL Server认证要提供用户名和密码图18-10 Save DTS Package对话框
单击下一步在Completing the DTS Import/Export Wizard对话框中单击完成结束包的创建操作如图18-11所示11
在步骤4的Specify Table Copy or Query对话框中如果选中Use a Query to specify thedata to transfer选项单击下一步之后打开Type SQL Statement对话框如图18-12所示在Query statement下的空白框中输入SELECT语句则该查询语句的结果集就是所有转换数据可以通过单击Query Builder按钮来激活创建查询向导
图18-11 Completing the DTS Import/Export Wizard对话框
图18-12 Type SQL Statement对话框
在步骤4的Specify Table Copy or Query对话框中如果选中Copy object and databetween SQL Server Database表示在源与目标数据源间传递数据对象包括表视图存储过程参照完整性索引单击下一步打开Select Objects to Transfer对话框如图18-13所示
图18-13 Select Object to Copy对话框
其中各选项的含义为
Create destination objects
在目标数据源中为所有传递对象创建目标对象
Drop destination objects first在创建新对象之前删除所有与源对象相同的目标对象
Include all dependent objects包括所有的依赖对象比如视图引用的基本表 Copy data选中该复选框表示允许从源向目标数据源拷贝数据
Replacing existing data用指定源数据源的数据覆盖目标数据源中的数据 Append data保留目标数据源中的原有数据并添加从数据源拷贝来的数据 Transfer all objects传递数据源中的所有数据库对象 Select objects只传递被选择的数据库对象 Options设置高级传递选项
Script file directory指定执行传递操作的SQL语句存储的目录
单击下一步继续包创建余下的创建步骤大体雷同
例18-2在该例中利用DTS Wizard向数据库中导入一个文本文件骤与例1基本相同下面主要介绍不同的创建步骤
1
其创建的步
建立源连接在例18-1的Step2的Choose a Data Source对话框中在Source旁的下拉列表中选择Text File选项然后输入文件的存储位置如图18-14所示
2设定文件格式单击下一步打开Select File Format对话框如图18-15所示
图18-14 Choose a Data Source对话框
图18-15 Select File Format对话框
Delimited符分隔 Fixed Field
把文本中的数据分配成不同的字段所有的字段必须以相同的结束
不同的字段宽度则执行第四步
把文本中的数据分配到具有相同宽度的字段中
则应执行第三步
如选中Fixed Field
可以不同
如果选中Delimited选项3
确定分界符单击下一步打开Specify Column Delimiter对话框如图18-16所示在该对话框中确定分界符来将文件中的文件按记录逐条分开成多列这里提供了常见的符号也可以在Other输入框中输入自定义的分界符
图18-16 Specify Column Delimiter对话框
4567定义每一字段的长度为导入数据输入目标表定义数据转换要求执行数据导入18.3 DTS Designer
DTS Designer与DTS Wizard一样都是在同构或异构数据源间进行数据的导入导出和转换但是DTS Designer是一个图形工具它使创建和编辑DTS包的工作变得更简单轻松而且提供了比DTS Wizard更为强大的功能
DTS Designer中包含几个关键性概念如连接数据传递这些概念对于理解DTS Designer是如何工作很有帮助
包
优先权约束任务
18.3.1 创建DTS Designer包
在包中不仅要指明源和目标数据源而且还要定义多个步骤每个步骤执行的任务在某些情况下还包括步骤优先权约束使用DTS Designer创建包首先要添加连接每个包包含目标连接和源连接
在连接中指明OLE DB提供者数据源
接着定义源和目
标连接间的数据转换然后要定义包将执行的任务也可以自定义任务最后决定是否运行包或将其存储以备后用
下面以具体地转换authors表为例子来说明如何使用DTS Designer来创建包而且在该例中我们要求将authors的au_fname列和au_lname 列合并成目标表desauthors的au_name列
18.3.2 添加连接
首先为包添加源与目标连接每个连接中指出了数据源的驱动程序OLE DB提供者所提出的要求不同
1
当然不同的
启动SQL Server Enterprise Manager后登录到指定的服务器右击DataTransformation Services文件夹在弹出菜单中选择New package选项打开DTS Package for SQL Server选项打开Connection Properties对话框如图18-17所示图18-17 Connection Properties 对话框 18.3.3 定义数据转换 数据转换任务是将数据从源连接传递到目标连接的主要机制每个数据转换任务都要引用一个DTS Data Dump和OLE DB服务提供者 创建完源与目标连接后应创建数据转换其方法是按住 以此来指明数据的 图18-18 创建数据转换任务 18.3.4 数据转换任务属性设置 1 创建完数据转换接着要进行转换的属性设置右击源和目标连接之间的箭头在弹出菜单中选择Properties选项打开Transform Data Task Properties对话框选中Source 标签页如图18-19所示在Source标签页可以选择来自源连接的数据Source标签页图18-19 Transformation Data Task Properties对话框 – Table name表示来自源连接的一张表 SQL Query表示来自源连接的一个查询结果集 2选中Destination标签页如图18-20所示在Table name旁的下拉列表中选择目标表或Create New按钮创建新表在此单击Create New按钮在弹出对话框中输入如下内容Destination标签页图18-20 Transform Data Task Properties对话框 – CREATE TABLE [desauthors] [au_id] varchar [phone] char [city] varchar [state] char [zip] char 52122011 NOT NULL,5040 NOT NULL, NULL, [au_name] varchar [address] varchar NOT NULL, NULL, NULL, NULL, [contract] bit NOT NULL 选中Transformations标签页如图18-21所示该对话框允许定义源连接与目标连接列之间的映射关系可以定义以下几个映射关系一对一多对一或一对多在本例中进行映射要执行以下操作 如果源列与目标列之间已存在映射则选中两者间的箭头然后按 3 Transformations标签页图18-21 Transform Data Task Properties对话框 – 按下 单击New按钮在弹出的Create New Transformation对话框中选择ActiveX script选项 单击OK按钮 打开Transformation Options对话框 如图18-22所示 图18-22 Transformation Options对话框 4 分别选中Source columns和Destination columns标签页从中选择准备转换的列如果源与目标连接的列映射存在多对一或一对多的关系则通过脚本语言可在转换过程中对源数据列进行加工从而让转换结果满足用户的要求再单击Properties按钮打开Active Script Transformation Properties 对话框如图18-23所示图18-23 Active Script Transformation Properties对话框 在空白区域输入如下VB代码 '**********************************************************************' Visual Basic Transformation Script '**********************************************************************' Copy each source column to the destination columnFunction MainDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationDTSDestinationEnd Function \"contract\"\"zip\"\"state\"\"city\"\"phone\"\"au_id\" = DTSSource \"contract\" = DTSSource = DTSSource = DTSSource = DTSSource = DTSSource \"zip\"\"state\"\"city\" \"address\"\"phone\"\"au_fname\" + DTSSource \"au_lname\" \"au_id\" \"address\"\"au_name\" = DTSSource = DTSSource Main = DTSTransformStat_OK 5选中Lookups标签页如图18-24所示Lookup标签页图18-24 Transform Data Task Properties对话框 – 6选中Options标签页如图18-25所示Options标签页图18-25 Transform Data Task Properties对话框 – 18.3.5 定义任务 在完成数据转换属性设置之后可以向包中添加自定义任务其操作步骤为 1从左边的任务栏中将要添加的任务类型拖到DTS Designer工作区然后右击进行属性设置其属性对话框如图18-26所示在本例中所定义的任务是SQL脚本任务图18-26 Execute SQL Task Properties 对话框 2 添加自定义任务之后则应该在转换数据任务和自定义任务上定义优先级条件其操作顺序为先后选择自定义任务和某个源连接然后选择Workflow 菜单的On Success选项此时如图18-27所示图18-27 定义优先级条件 3在完成包的创建之后可以单击工具栏上的Run按钮来运行包在包执行时可以通过暂停和停止按钮来对执行过程进行控制如图18-28所示图18-28 运行包窗口 18.4 本章小结 在本章中我们主要介绍DTS的若干问题重点讨论了如何使用导入/导出向导以及DTS Designer来实现数据或数据库对象的转换 因篇幅问题不能全部显示，请点此查看更多更全内容键删除两者间的映射