第五章DTS 数据挖掘

1、1第五章第五章 SQL Server 数据转换服务数据转换服务2SQL Server数据转换服务 创建数据仓库的一大挑战就是在构创建数据仓库的一大挑战就是在构建好数据仓库之后的数据装入工作。建好数据仓库之后的数据装入工作。 SQL SERVER 2000增加了一套新增加了一套新的工具的工具DTS（Data Transformation Services,数据转换服务），支持有序而数据转换服务），支持有序而有控制的导入数据。有控制的导入数据。3DTS中最常用的两大工具是DTS向导与DTS设计器，DTS向导提供了循序渐进的指引功能，依序的指示用户进行数据导入、导出以及转换的工作。我们将于本章使用D

2、TS设计器介绍数据转移的工作，因为他的功能比DTS向导更强大。SQL Server数据转换服务4 5.1 DTS设计器简介 DTS设计器提供了图形客户机接口，用户可以将所需要的对象用鼠标拉出来，并且在来源数据与目的数据之间进行转换的工作，比起DTS向导，DTS设计器的弹性大得多。 DTS设计器可以让我们将转换过程存储成包（package），以方便重复使用。5 数据转移任务的步骤： 设置数据源 设置数据目的地 设置转换方式（如果有需要的话） 将数据转移任务存储为一个包 执行包实际进行数据转移5.1 DTS设计器简介 6 5.2 5.2 新建目的数据库新建目的数据库在我们正式开始执行数据转换之前，

3、首先建立一个新的目的数据库，用来存放数据仓库的数据。我们为该数据库命名为foodmart_dw。 在Enterprise Manager上右击【数据库】图标在快捷菜单中选取【新建数据库】选项。7 5.2 5.2 新建目的数据库新建目的数据库8 5.3 5.3 激活激活DTSDTS设计器设计器激活DTS设计器可以开始设计数据转换的任务。在Enterprise Manager 上右击【数据转换服务】图标，在快捷菜单中选取【新建包（P）】选项。9 5.3 5.3 激活激活DTSDTS设计器设计器10 5.3 5.3 激活激活DTSDTS设计器设计器11 5.4 5.4 创建数据源与目的地创建数据源与

4、目的地在进行数据转移之前，首先要设置的就是数据源与数据目的地。就我们的示范数据仓库而言，源数据库是示范数据库foodmart，而目的数据库库则是我们刚刚创建的foodmart_dw，到目前为止，他还没有任何表。sql server analysis services为我们提供了一个实验数据库foodmart,是ACCESS格式。我们需要将它利用DTS转换成SQL SERVER格式。12 单击Microsoft ACCESS 图标 5.4 5.4 创建数据源与目的地创建数据源与目的地13 单击Microsoft ACCESS 图标 5.4 5.4 创建数据源与目的地创建数据源与目的地14 5.4

5、 5.4 创建数据源与目的地创建数据源与目的地15 同理，我们创建sql server的目的数据库 5.4 5.4 创建数据源与目的地创建数据源与目的地16 同理，我们创建sql server的目的数据库 5.4 5.4 创建数据源与目的地创建数据源与目的地17 5.4 创建数据源与目的地 5.4 5.4 创建数据源与目的地创建数据源与目的地18 5.5 5.5 转移表转移表此处我们以转移CUSTOMER表为例来说明这个问题。我们需要创建一个【转换数据任务】。19 步骤步骤1：单击：单击microsoftaccess步骤步骤2：先按住：先按住ctrol键，再单击键，再单击步骤步骤3：单击：单击

6、“转换数据任务转换数据任务”按钮按钮 5.5 5.5 转移表转移表20 DTS设计器在设计器在sqlserver和和access之间创建了一个之间创建了一个带箭头的指针，箭头位置带箭头的指针，箭头位置指向目的数据库。转换数指向目的数据库。转换数据任务是具有方向性的，据任务是具有方向性的，所以单击数据连结的顺序所以单击数据连结的顺序是很重要的。是很重要的。 5.5 5.5 转移表转移表21 在我们的应用中没有从access数据库中拷贝所有的列，这样在传输数据时有两种选择： 使用T-SQL命令来从数据源读取数据 选择customer表，使用transformation选项卡移走不必要的列。 5.5

7、 5.5 转移表转移表22 源数据 可以直接使用一个已知的表 5.5 5.5 转移表转移表23 源数据-可以使用一个SQL语句确定原数据的内容。 5.5 5.5 转移表转移表24 目的地如果目的表存在，可以直接选择 5.5 5.5 转移表转移表25 目的地如果目的表不存在，可以在此处直接创建。 5.5 5.5 转移表转移表26 转换 5.5 5.5 转移表转移表27 转换虽然我们第一次单击转换标签，但是系统已经出虽然我们第一次单击转换标签，但是系统已经出现了一些转换，但是实际上系统并没有为我们定现了一些转换，但是实际上系统并没有为我们定义好转换。因此，如果微软不显示这些箭头，误义好转换。因此，

8、如果微软不显示这些箭头，误会可能会少一些。会可能会少一些。 5.5 5.5 转移表转移表28 转换单击全部删除单击全部删除 5.5 5.5 转移表转移表29 转换单击单击“新建新建” 5.5 5.5 转移表转移表30 转换选择列之间的选择列之间的直接拷贝直接拷贝 5.5 5.5 转移表转移表31 转换依次选择源列和目标列依次选择源列和目标列 5.5 5.5 转移表转移表32 转换 5.5 5.5 转移表转移表33 转换 5.5 5.5 转移表转移表34 转换 5.5 5.5 转移表转移表35 转换单击确定单击确定 5.5 5.5 转移表转移表36 转换 5.5 5.5 转移表转移表37 转换这

9、时布局已经作了改变，以反映列的映射。源数据的映射这时布局已经作了改变，以反映列的映射。源数据的映射都集中成一条线，然后又发散开。正是中间的那条线决定都集中成一条线，然后又发散开。正是中间的那条线决定了转换选型卡式如何工作的。了转换选型卡式如何工作的。 5.5 5.5 转移表转移表38 转换单击确定单击确定 5.5 5.5 转移表转移表39 5.6 5.6 保存包保存包40 保存包的过程很直接。单击磁盘图标，或者从保存包的过程很直接。单击磁盘图标，或者从package菜单菜单中选择中选择save，这时就会弹出关于包的细节信息的窗口。，这时就会弹出关于包的细节信息的窗口。 5.6 5.6 保存包保

10、存包41 5.6 5.6 保存包保存包42 5.6 5.6 保存包保存包43 5.7 5.7 包的执行包的执行单击执行单击执行44 5.7 5.7 包的执行包的执行45 数据转换服务数据转换服务包可以重复执行多遍，但必须清除上一次运行时留在数据库中的信息。这样包将删除将要创建的数据库中的所有表，但会保留其它用户自建的表。因此，在前面的例子中，我们首先利用SQL任务实现表的删除，重新创建，最后在进行数据的转换，从而保证数据的正确性。46 5.8 5.8 删除表删除表首先删除数据库中所有的ACCOUNT表的实例。47 首先删除数据库中所有的ACCOUNT表的实例。 5.8 5.8 删除表删除表48

11、 首先删除数据库中所有的ACCOUNT表的实例。 5.8 5.8 删除表删除表49 删除了数据库中的account之后，我们要重新建立一个新的account表。可以将这两个任务（删除和创建表）放在一个任务中，但如果表不存在，那么第一个动作会产生错误，则第二个动作就不能执行了，或者会将产生的错误传递到下一步中。因此，最好将它们放在两个任务中。 5.8 5.8 删除表删除表50 5.8 5.8 创建表创建表51 5.8 5.8 创建表创建表52 5.8 5.8 创建表创建表53 5.9 5.9 创建工作流创建工作流54 面对一个包里的多个任务，我们需要建立工作流来指定任务面对一个包里的多个任务，我

12、们需要建立工作流来指定任务的执行过程和执行条件。的执行过程和执行条件。 5.9 5.9 创建工作流创建工作流55 删除当前数据库中的删除当前数据库中的account表表创建一个新的创建一个新的account表表进行进行account表的数据表的数据转换转换 5.9 5.9 创建工作流创建工作流56 数据流的建立 工作流有三种，表示执行的条件： 完成时 成功时 失败时 5.9 5.9 创建工作流创建工作流57 工作流的建立第一步：按住第一步：按住shift，先后单击，先后单击delete 和和create 任务任务第二步：单击工作流第二步：单击工作流 5.9 5.9 创建工作流创建工作流58 5

13、.9 5.9 创建工作流创建工作流59 根据我们前面的分析，当第一次执行这个包的根据我们前面的分析，当第一次执行这个包的时候，时候，account表并不存在，因此这个删除任表并不存在，因此这个删除任务可能不能成功执行。但是务可能不能成功执行。但是create table一定一定要执行，因此，我们选择一个完成时工作流。要执行，因此，我们选择一个完成时工作流。 5.9 5.9 创建工作流创建工作流60 然后在然后在create table 和数据转换任务之和数据转换任务之间建立成功时数据流间建立成功时数据流 5.9 5.9 创建工作流创建工作流61 5.9 5.9 创建工作流创建工作流62 包的执

14、行第一次执行 5.9 5.9 创建工作流创建工作流63 包的执行第二次执行 5.9 5.9 创建工作流创建工作流64 5.10 5.10 设置表的主键域外键设置表的主键域外键65 单击管理关系图标，设定外键单击管理关系图标，设定外键 5.10 5.10 设置表的主键域外键设置表的主键域外键66 5.10 5.10 设置表的主键域外键设置表的主键域外键67 5.11 5.11 数据转换中公式的使用数据转换中公式的使用如：目标表的一个字段可以是源表多个属性构成的表达式的值。这个功能我们可以通过在转换的过程中利用ActiveXScript实现68 数据转换的过程中可以使用公式源表源表 5.11 5.

15、11 数据转换中公式的使用数据转换中公式的使用69 数据转换的过程中可以使用公式目的表目的表Account_id=a+b 5.11 5.11 数据转换中公式的使用数据转换中公式的使用70 数据转换的过程中可以使用公式 5.11 5.11 数据转换中公式的使用数据转换中公式的使用71 5.1.7 数据转换的过程中可以使用公式1. 在左侧的在左侧的【源源】标签中同时选中标签中同时选中a,b字段（按住字段（按住Ctrl键不键不放）放）2. 在右侧的在右侧的【目的目的】列表框中单击列表框中单击account_id字段字段3. 单击单击【新建新建w】按钮按钮 5.11 5.11 数据转换中公式的使用数据

16、转换中公式的使用72 数据转换的过程中可以使用公式选择转换方式选择转换方式 5.11 5.11 数据转换中公式的使用数据转换中公式的使用73 数据转换的过程中可以使用公式转换选项的常规选项卡转换选项的常规选项卡 5.11 5.11 数据转换中公式的使用数据转换中公式的使用74 数据转换的过程中可以使用公式在转换选项的常规选项在转换选项的常规选项卡中，单击卡中，单击“属性属性” 5.11 5.11 数据转换中公式的使用数据转换中公式的使用75 数据转换的过程中可以使用公式选择语言：选择语言：vb，java 5.11 5.11 数据转换中公式的使用数据转换中公式的使用76 数据转换的过程中可以使用

17、公式 5.11 5.11 数据转换中公式的使用数据转换中公式的使用77 数据转换的过程中可以使用公式* Visual Basic 转换脚本转换脚本* 将各源列复制到目的列将各源列复制到目的列Function Main()DTSDestination(account_id) = DTSSource(a) + DTSSource(b) Main = DTSTransformStat_OKEnd Function 5.11 5.11 数据转换中公式的使用数据转换中公式的使用78 数据转换的过程中可以使用公式单击单击“分析分析”验证表达式是验证表达式是否正确否正确 5.11 5.11 数据转换中公式的

18、使用数据转换中公式的使用79 数据转换的过程中可以使用公式单击单击“分析分析”验证表达式是验证表达式是否正确否正确 5.11 5.11 数据转换中公式的使用数据转换中公式的使用80 5.12 5.12 数据转换服务数据转换服务小节小节 SQLSERVER 2000提供了数据转换服务，它是一套功能强大的数据转换工具，可以在SQL与其它的OLDB、ODBC数据源以及文字文件之间执行数据导入导出以及转换的服务。 DTS中最常用的两大工具是DTS向导与DTS设计器。 DTS向导提供了循序渐进的指引功能，依序的指示用户进行数据导入、导出以及转换的工作。 DTS设计器提供了图形客户机接口，用户可以将所需要的对象鼠标拉出来，并且在源数据与目的