一问题描述
某事业单位很早以前开发了一套基于Oracle数据库的管理系统工作在WINDOWS 下采用C/S工作模式数据库的字符集为WEISOP由于工作需要需开发一套在此基础上的查询系统为保证原系统的安全和完整性要求查询系统不得直接使用原数据库影响目前系统的运行只能通过中间件技术实现查询系统对原数据库的访问同时由于原系统在使用过程中发现数据存取的速度很慢要求查询系统使用SQL SERVER 数据库进行查询
二解决方案分析
根据用户的需求和原系统的工作模式可采用的方案主要有以下三种
利用SQL SERVER 的作业调度功能定时执行数据迁移实现数据同步
DTS（数据转换服务）是微软从SQL SERVER 开始引入的DTS的主要目的是在系统之间迁移数据和数据库对象DTS原来是用作SQL SERVER OLAP服务的ETL工具后来微软意识到DTS 不仅可以作为OLAP 服务的数据抽取和载入工具还可以实现异种数据库间的迁移因此扩充了DTS的功能在SQL SERVER 中提供了简单易用的DTS 设计器利用DTS设计器可以很方便地解决本文涉及的问题但是如果要迁移的对象比较多利用DTS设计器的工作量就相当大了因此提出了第二种解决方案
利用DTS 编程实现数据的定时迁移
该方法原理简单但需要对 DTS 有一定的了解性能也比较好熟悉VBVCDELPHI等任一种编程语言均可以利用SQL Server 提供的 DTS COM接口实现数据的迁移
DTS 迁移规范保存在一个称为包的实体中DTS包是基本的DTS组件的容器这些组件包括连接任务转换工作流不同的组件完成不同的功能它们共同构成数据迁移的实现主体要通过DTS编程实现数据库的迁移至少需要两个连接对象其中一个提供数据一个接收数据至少需要一个转换对象完成数据从源到目的服务器的转换至少需要两个任务对象完成迁移之前的目的服务器上的数据表的删除和重建至少需要三个工作流对象为迁移工作设计执行的步骤
为了实现定时执行程序还要完成对SQL SERVER AGENT 进行编程实现迁移作业的提交和调度由于SQL SERVER 的作业调度是通过 SQL SERVER AGENT 来管理的因此需要在启动SQL SERVER 时同时启动SQL SERVER AGENT
采用中间件技术
前面两种方案都是利用DTS离不开SQL Server 的DTS利用中间件技术可以通过实现一服务程序定时将数据从ORACLE服务器取出然后转换成SQL SERVER 数据库的数据格式传入SQL SERVER其工作原理如下图
该技术可以通过通过ODBC 或OLE DB技术编程实现数据的定时获取和转换传出对于编程的工作量较大原因在于ORACLE 和SQL SERVER的数据类型的不一致必须通过类型转换实现数据的一致同时效率也比较低由于作为一种服务程序长驻内存对程序的质量要求至少不得出现内存洩露否则可能使服务器瘫痪不过这种方案的好处在于可以脱离SQL SERVER 维护的工作量相对要轻一些
比较上述的三种方案从实现的难度上比较第一种最低最后一种最高从效率上比较最后一种最低第一种与第二种最高从可维护性来比较第一种最低最后一种最高
综合三种方案笔者认为第二中方案较好发布到目标系统上只需在现场运行一次数据迁移的任务安装程序就能实现SQL Server 定时从Oracle服务器迁移数据同时所有的工作量也只是选择要迁移的数据表该工作如果要迁移的表是已知的甚至可以从文件中直接读入就能实现任务的安装
下面介绍采用第二种方案用VB编写在SQL SERVER上能定时自动进行数据迁移任务的安装程序的方法
三数据迁移的实现
为了能在目标机上顺利实现数据迁移将DTS包存储到SQL SERVER在SQL SEVRE AGENT的作业调度中采用DTSRUN 来加载和执行DTS包这样所有的工作只需作一次就可将整个数据迁移的DTS包和SQL SERVER的作业发布到目标机上
（一）算法设计
程序的流程图如下
（二）关键技术说明
要实现数据的迁移必须考虑两个问题第一数据的迁移要求目标系统上的数据与ORACLE数据库中的数据要一致因此目标数据库中的相应表必须在迁移之前被删除所以迁移任务的第一个是对相应表执行删除的SQL 任务第二由于目标表被删除迁移的数据失去寄托因此迁移任务的第二步必须在目标系统上重建相应的表在建表时由于ORACLE 数据库的数据类型与SQL SERVER 不一致因此必须进行类型转换它们之间的对应关系和转换要求如下表
（三）程序设计
界面设计如图所示
编码
() 在整个程序运行过程中需要两个全局变量DTS 包 oPackage 和Oracle 服务器连接 oraCon
当设置好连接参数后单击连接Oracle服务器将与 ORACLE 服务器连接并取出所有表然后填充第一个列表框并在下面表格中显示相应的数据（如果选中了显示数据）
Private Sub Command_Click()
Dim rst As New ADODBRecordset
With oraCon
Provider = OraOLEDBOracle
Properties(User ID) = Text()Text
Properties(Password) = Text()Text
Properties(Data Source) = Text()Text
Properties(Persist Security Info) = True
Open
End With
With rst
Source = select * from all_tables where tablespace_name<>SYSTEM order by ownertable_name
ActiveConnection = oraCon
CursorType = adOpenKeyset
LockType = adLockOptimistic
Open
End With
RefreshGrid rst
FillTabList rst
rstClose
CommandEnabled = False
End Sub
() 在第一个列表框中双击数据表列表将选中的表加入第二个列表如果显示数据被选中将显示相应表的数据同时显示表的结构信息在第二个列表框中双击数据表将选中项移出该列表框
Private Sub List_DblClick()
Dim rst As New ADODBRecordset
Dim strSQL As String
Dim tmpStr As String
ListAddItem (ListList(ListListIndex))
If CheckValue = Then
With rst
Source = select * from & ListList(ListListIndex)
ActiveConnection = oraCon
CursorType = adOpenKeyset
LockType = adLockOptimistic
Open
End With
RefreshGrid rst
rstClose
End If
tmpStr = ListList(ListListIndex)
strSQL = SELECT COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
strSQL = strSQL & FROM SYSALL_TAB_COLUMNS WHERE TABLE_NAME=
strSQL = strSQL & & Mid(tmpStr InStr( tmpStr vbTextCompare) + ) &
strSQL = strSQL & and OWNER= & Mid(tmpStr InStr( tmpStr vbTextCompare) ) &
With rst
Source = strSQL
ActiveConnection = oraCon
CursorType = adOpenKeyset
LockType = adLockOptimistic
Open
End With
rstMoveFirst
RefreshPropGrid rst
rstClose
PropGridVisible = True
SQLScriptListVisible = False
End Sub
Private Sub RefreshGrid(rst As ADODBRecordset)
Dim fld As ADODBField
On Error Resum
