DTS数据转换服务的使用

 

本人转载 原文地址：http://blog.sina.com.cn/s/blog_59ca2c2a0100v85r.html

在SQL Server的企业管理器中有一个数据转换服务(Data Transfer Service)，它主要用作在不同的数据库之间转换数据，比如在SQL Server和Oracle之间转换数据。下边我们就以将Oracle中的数据转换到SQL Server中为例做一下介绍。
    首先启动SQL Server的企业管理器，如下图所示：

 

进入SQL企业管理器的控制台界面，打开服务器节点，点开里边的数据转换服务节点，右击“本地包”节点，选择“新建包”菜单。如下图所示：

 

    进入新建DTS包界面，如下图所示：

 

    窗口的左边是建立连接和任务的快捷工具栏，上边是菜单栏，右侧是主窗口。

DTS的主要任务是将数据从一个数据库传输到另一个数据库，既然要访问数据库，那么肯定要有连接才行，所以第一步就是建立连接。我们以从Oracle数据库中转换数据到SQL Server数据库为例做介绍。点击“连接”菜单，选择“Microsoft OLE DB Provider for SQL Server...”，如下图所示：

 

    弹出连接属性对话框，在“新建连接”后边输入连接的名称，在服务器下拉框中选择要连接的服务器，输入用户名和密码，选择要连接的数据库，如下图所示：

 

    点击“确定”按钮，即建立了对目标数据库的连接。接下来我们建立源数据库的连接，源数据库是Oracle，点击“连接”菜单，选择“Microsoft ODBC Driver for Oracle...”，如下图所示：

  

    在弹出的“连接属性”对话框中点击“属性”按钮，如下图所示：

 

    弹出“数据连接属性”对话框，在里边输入Oracle数据库的名称、用户名和密码，点击“测试连接”按钮，测试连接是否成功。如下图所示：

 

测试连接成功之后，点击“确定”按钮，在主窗口中出现我们刚才建立的两个连接，如下图所示：

 

在将源数据库导入到目标数据库之前，我们先把目标数据库表中的数据删除，然后再导入数据。我们需要先建立一个删除数据的任务，拖一个任务图标到主窗口中，如下图所示：

 

    双击这个图标，会弹出“执行 SQL 任务属性”对话框，在描述文本框中输入这个图标的文本描述，在现有连接下拉框中选择我们刚才建立的SQL Server数据库的连接，因为我们删除目标数据库SQL Server中的数据，所以此处选择SQL Server数据库的连接。在SQL语句文本框中输入删除数据的SQL语句，点击“确定”按钮，这个删除数据的任务就完成了。

先前我们已经建立了Oracle数据库的连接，将该图标放在删除原有数据图标的右侧，如下图所示：

 

    将先前建立的SQL Server数据库的连接图标放在Oracle数据库连接图标的右侧，如下图所示：

 

接下来准备建立转换数据的任务，点击左侧任务图标栏中的转换数据任务图标，如下图所示：

 

点击数据转换任务图标后，首先要选择源连接，此处我们是将Oracle中的数据导入到SQL Server中，所以源连接就是先前我们建立的Oracle连接，目标连接就是SQL Server连接。注意选择源连接和目标连接时不能用鼠标进行框选，也就是不能用鼠标画一个方框的方式将图标框在里边来选择，而是要点击连接图标去选取源连接和目标连接。选择源连接，如下图所示：

 

再点击目标连接，如下图所示：

 

这样源连接和目标连接之间就会多一条代表数据转换任务的连线，如下图所示：

 

双击该任务连线，弹出“转换数据任务属性”对话框，如下图所示：

 

源选项卡中主要定义源连接的SQL查询语句。在描述文本框中输入对源连接进行的操作描述文本，在连接单选钮中选择“SQL 查询”，然后再下边的文本框中输入查询语句。该查询语句查出的记录会导入到目标数据库中。如果选择“表/视图”的话，就需要在右边的下拉框中选择相应的表或者视图名称，DTS就会将该表中的所有记录或者视图查出的所有记录全部导入到目标数据库中。

定义完源，接下来定义目标。点击“目的”选项卡，如下图所示：

 

在表名下拉框中选择要导入数据的目的表，选择了目的表之后，在下边会显示该表的结构。设置了从源表中提取数据的查询SQL语句和到插入数据的目的表之后，就该设置转换的方式了。点击“转换”选项卡，如下图所示：

 

左边的源显示源查询SQL中查出的字段，右边显示目的表中的字段，此处要指定源和目的字段的对应关系。如果源和目的中的字段名相同的话，DTS会自动建立连线，表示从源中查出的这个字段插入到目的表中的对应字段中。如源中有一个“IDNO”字段，目的中也有一个“IDNO”字段，那么DTS会在这两个字段间建立连线，表示从源SQL中查出IDNO的值插入到目的表中的IDNO字段中。当然源和目的表中的字段名不一定相同，这个时候就需要手动建立源和目的之间的联系，让DTS知道从源中查出的值插入到目的表的哪个字段中。双击连线，弹出“转换选项”对话框，如下图所示：

 

在“转换选项”对话框中点击“属性”按钮，弹出“列顺序”对话框，在里边选择源和目的列的对应关系，或者点击“转换选项”对话框中“源列”和“目的列”，设定好后点击“确定”按钮。

在数据导入完成之后我们会更新目标数据库表中的状态，所以接下来一个执行SQL的任务，用于执行更新数据的任务。拖一个任务图标到主窗口中，然后双击该图标，在弹出的“执行 SQL 任务属性”窗口中选择数据库连接，输入要执行的SQL语句，如下图所示：

 

输入完成后点击“确定”按钮，如下图所示：

 

接下来我们需要将几个图标连接起来，组成一个完整的执行过程。我们要求在数据导入目标表成功之后再更新目标库中的数据状态，所以点击“工作流-->成功时”菜单，如下图所示：

 

将SQL Server目标库图标和更新数据的图标用成功时连线连接起来，如下图所示：

 

双击成功时连线，弹出“工作流属性”对话框，如下图所示：

 

 可以在优先下拉框中选择状态。图中我们选择成功，表示只有在数据导入成功的时候才更新状态，其它情况不更新状态。点击“确定”按钮，然后用相同的方法将删除原来的数据图标和Oracle源数据库连接图标连接起来，如下图所示：

 

最终效果如下图所示：

 

接下来我们将这个DTS包保存起来，点击“包-->保存”菜单，如下图所示：

 

弹出“保存 DTS 包”对话框，如下图所示：

 

输入包名称，选择保存服务器、登录的用户名和密码，点击“确定”按钮，在“本地包”中就会出现我们刚才建立的DTS服务包，如下图所示：

 

到此这个DTS包就算完成了，但是它不会自己执行，所以我们还需要建立一个作业，让SQL Server在指定的时间去调用这个DTS包，来完成我们在包中定义的数据转换任务。右击刚才我们新建的DTS包，在右键菜单中选择“调度包...”菜单，如下图所示：

 

在弹出的“编辑反复出现的作业调度”对话框中设置作业调度的频率及时间等参数，如下图所示：

具体设置见下边的新建作业说明。设置好后，在“管理-->SQL Server代理-->作业”中会出现上边新建的作业，下来我们可以对这个作业进行编辑。双击这个作业，弹出新建作业的对话框，如下图所示：

 

在“常规”选项卡中输入作业名称，选择所有者，选中“启用”复选框。点击“调度”选项卡，准备新建作业调度，如下图所示：

 

点击“新建调度”按钮，弹出“新建作业调度”对话框，如下图所示：

 

在里边输入作业调度的名称，选中“启用”复选框，在“调度类型”框中选择调度的频率。如果只执行一次，那么可以选择“一次”，然后选择执行的日期和时间。因为我们需要每天都从Oracle中导入数据，所以我们选择“反复出现”。然后点击“更改”按钮，弹出“编辑反复出现的作业调度”对话框，如下图所示：

 

选择发生频率，如果选择“每天”的话，则在下边的“每日频率”中选择发生的时间。如果选择“每周”，则在右边选择在星期几启动。在“持续时间”中选择开始日期和结束日期。然后点击“通知”选项卡，选择作业完成时的通知方式，如下图所示：

 

如果想在作业执行完成之后电子邮件通知的话可以选择“电子邮件操作员”。如果选择“写入 Windows 应用程序事件日志”的话，则会在作业失败或者成功的时候记录在Windows的事件日志中。这样作业调度就设置完了，DTS系统会在指定的时间启动作业，来完成我们设定的数据转换任务。

我们也可以手动调用一下这个作业，看一下我们的作业是否正常。右击我们新建的作业，在右键菜单中选择“启动作业”菜单，如下图所示：

 

然后DTS就会启动作业，完成之后会显示成功或者失败，并记录下来。右击新建的作业，选择“查看作业历史记录”，如下图所示：

 

弹出“作业历史记录”对话框，用户可以查看作业的执行情况，如下图所示：