异构库环境的ETL,oracle VS mssql

xiaui520

　　1、环境需求描述：
　　用户内网有一台oracle 10g库，外网有一台oracle 9i。用户通过内网发布人才招聘相关信息，所有的信息在一张表里面，运行一个export/import脚本把内网该表同步到外网表，内网和外网之间应该 是建立了临时表lb_retain，这张表是作为外网应用使用。该用户开发的新CMS系统（ASP.NET），数据库是采用MSSQL SERVER 2005 X64，该数据库也是位于外网环境中。由于用户发布招聘信息不想在新CMS平台上进行，所以不得不对这一信息进行整合。最终要使该招聘信息能在新网站上显 示给最终用户。
　　2、解决思路：
　　分析以上需求，即源oracle库同 步到目标mssql 2005，为了达到异构库环境的表与表之间的数据同步，本次解决方案是采用了MSSQL商业智能解决方案，用SQL Server Business Intelligence Development Studio工具创建Integration Services 项目创建DTS包，该包可以处理非常复杂的数据挖掘工作（如果源是mssql，目标是oracle的话，可以使用oracle的透明网关）。其中，我建立 从oracle抽取数据并插入到新网站所对应的MSSQL表招聘里面。这样就达到了整合的效果，只要拿到了数据插入到新应用所在的库里面去，就可以整合到 了新应用的所有功能，而不需要二次开发，也不需要写应用层代码来操作oracle库了。
　　3、解决过程
　　(1)新建Integration services项目

　　(2)SSIS导入导出向导--建立基本的数据库连接串以及基本的SQL，选择源

　　（3）选择目标

　　（4）编写SQL语句

　　（5）建立源列到目标表的列的映射

　　（6）下一步，警告先不管

　　（7）分别添加“数据转换”、“派生列”、“脚本组件”
　　“数据转换”把源ORACLE的varchar2列转换为MSSQL所兼容的列

　　（8）“派生列”
　　根据你的业务不同配置也是不太一样的，这里所要添加“派生列”是因为源ORACLE库没有与目标库对应的列，所以要在同步的过程中生成新列并赋初值

　　（9）“脚本组件”
　　“脚本组件”是为了处理比较复杂的业务流程，比如本方案有一个字段是要自增的，所以要编写脚本
　　设置输入列
　　设计脚本：
　　ScriptMain:
　　' Microsoft SQL Server Integration Services user script component
　　' This is your new script component in Microsoft Visual Basic .NET

　　' ScriptMain is the entrypoint>　　Imports System
　　Imports System.Data
　　Imports System.Math
　　Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
　　Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

　　Public>　　Inherits UserComponent
　　Dim intCounter As Integer = 1 'Set intial value here
　　Public Overrides Sub 输入0_ProcessInputRow(ByVal Row As 输入0Buffer)
　　Row.Taxis = intCounter
　　intCounter = intCounter + 1 ' Set the incremental value here
　　End Sub

　　End>　　BufferWrapper
　　' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!
　　' Microsoft SQL Server Integration Services buffer wrappers

　　' This module defines>　　' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!
　　Imports System
　　Imports System.Data
　　Imports Microsoft.SqlServer.Dts.Pipeline
　　Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

　　Public>　　Inherits ScriptBuffer
　　Public Sub New(ByVal Buffer As PipelineBuffer, ByVal BufferColumnIndexes As Integer())
　　MyBase.New(Buffer, BufferColumnIndexes)
　　End Sub
　　Public Property [Taxis]() As Int32
　　Get
　　Return CType(Me(0), Int32)
　　End Get
　　Set
　　Me(0) = Value
　　End Set
　　End Property
　　Public Property [Taxis_IsNull] As Boolean
　　Get
　　Return IsNull(0)
　　End Get
　　Set
　　SetNull(0)
　　End Set
　　End Property
　　Public Function NextRow() As Boolean
　　NextRow = MyBase.NextRow()
　　End Function
　　Public Function EndOfRowset() As Boolean
　　EndOfRowset = MyBase.EndOfRowset
　　End Function

　　End>　　ComponentWrapper
　　' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!
　　' Microsoft SQL Server Integration Services component wrapper

　　' This module defines the base>　　' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!
　　Imports System
　　Imports System.Data
　　Imports Microsoft.SqlServer.Dts.Pipeline
　　Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
　　Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

　　Public>　　Inherits ScriptComponent
　　Public Connections As New Connections(Me)
　　Public Variables As New Variables(Me)
　　Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As PipelineBuffer)
　　If InputID = 1572 Then
　　输入0_ProcessInput(New 输入0Buffer(Buffer, GetColumnIndexes(InputID)))
　　End If
　　End Sub
　　Public Overridable Sub 输入0_ProcessInput(ByVal Buffer As 输入0Buffer)
　　While Buffer.NextRow()
　　输入0_ProcessInputRow(Buffer)
　　End While
　　End Sub
　　Public Overridable Sub 输入0_ProcessInputRow(ByVal Row As 输入0Buffer)
　　End Sub

　　End>
　　Public>　　Dim ParentComponent As ScriptComponent
　　Public Sub New(ByVal Component As ScriptComponent)
　　ParentComponent = Component
　　End Sub

　　End>
　　Public>　　Dim ParentComponent As ScriptComponent
　　Public Sub New(ByVal Component As ScriptComponent)
　　ParentComponent = Component
　　End Sub

　　End>　　（10）打开“目标”的数据流
　　建立映射关系

　　（11）设置一下ETL包，不然等下添加JOB的时候有可能出错
　　“项目属性”-“调试”-“调试选项”-Run64BitRuntime=false(后面讲解释原因)
　　包的加密方式用-EncryptSensitiveWithPassword

　　（12）直接执行该包，看结果，没有错误，成功后就开始把它加入到MSSQL的JOB
　　打开SQL Server Management Studio并开启SQL SERVER代理，新建作业，详细怎么去创建可以做官网去查文档，这里只讲关键步骤：

　　在命令窗口里面输入：

• "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "C:\ETL\ETL\ETL.dtsx" /DECRYPT 123456 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
  

　　让DTExec.exe这个应用程序去调用刚刚创建好的ETL.DTSX包，/DECRYPT 123456为该包的密码。
　　最后，对（11）中的Run64BitRuntime=false做解释，这样操作表示将包做为32位处理。按官方说法我们只能创建一个32位的ETL.DTSX包
　　官方说法：
　　此外，某些 OLE DB 提供程序和某些 ADO.NET 提供程序不可用的 64 位版本。如果在包中使用这些提供程序，64 位 SQL Server 代理程序通过使用 64 位 DTExec.exe 实用程序运行包时出现连接的各种问题。
　　然后,调度 64 位 SQL Server 代理程序必须通过使用 32 位版本的 DTExec.exe 实用程序运行包。
　　官方理解，调度 64 位 SQL Server 代理程序：http://support.microsoft.com/kb/934653/zh-cn