ETL in Heterogeneous Database environments, oracle VS mssql

1. Environment Requirement Description:

The user has an oracle 10g Database on the Intranet and an oracle 9i database on the Internet. The user publishes information about talent recruitment through the Intranet. All information is stored in one table, and an export/import script is run to synchronize the table from the Intranet to the Internet table, A temporary table lb_retain should be created between the Intranet and the Internet. This table is used as an Internet application. The user developed the new CMS system ASP. NET), the database is mssql server 2005X64, the database is also located in the Internet environment. Because users do not want to publish recruitment information on the new CMS platform, they have to integrate this information. In the end, the recruitment information should be displayed to the end user on the new website.

2. solution:

Analyze the above requirements, that is, to synchronize data between tables in a heterogeneous database environment, from the source oracle database to the target mssql 2005 in the same step, this solution uses the MSSQL Business Intelligence solution. You can use the SQL Server Business Intelligence Studio tool to create an Integration Services project and create a DTS package, this package can handle very complex data mining work. If the source is mssql and the target is oracle, you can use the transparent gateway of oracle ). Here, I set up to extract data from oracle and insert it into the MSSQL table recruitment corresponding to the new website. In this way, the integration effect is achieved. As long as data is inserted into the database where the new application is located, all functions of the new application can be integrated without secondary development, you do not need to write application-layer code to operate the oracle database.

3. Solution Process

(1) Create an Integration services project

650) this. width = 650; "height =" 424 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U9533049-0.png "alt =" clip_image001 "title =" clip_image001 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

(2) SSIS Import and Export wizard-create a basic database connection string and basic SQL, select the source

650) this. width = 650; "height =" 423 "border =" 0 "src =" http://img1.51cto.com/attachment/201212/1/1645600_1354347379Mcn3.png "alt =" clip_image002 "title =" clip_image002 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

3) Select a target

650) this. width = 650; "height =" 410 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U9535944-2.png "alt =" clip_image003 "title =" clip_image003 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

4) Write SQL statements

650) this. width = 650; "height =" 380 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U953M44-3.png "alt =" clip_image004 "title =" clip_image004 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

5) create a ing between the source column and the target table.

650) this. width = 650; "height =" 416 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U953Ca-4.png "alt =" clip_image005 "title =" clip_image005 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

6) Next, warn you to ignore it first.

650) this. width = 650; "height =" 339 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U9533R4-5.png "alt =" clip_image006 "title =" clip_image006 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

7) Add "Data Conversion", "derived column", and "script component" respectively"

"Data Conversion" converts the varchar2 columns of the source ORACLE database to columns compatible with MSSQL.

650) this. width = 650; "height =" 395 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U9531023-6.png "alt =" clip_image007 "title =" clip_image007 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

8) "derived column"

Different Configurations vary depending on your business. The "derived column" is added here because the source ORACLE database does not have a column corresponding to the target database, therefore, new columns must be generated during synchronization and the initial values must be assigned.

650) this. width = 650; "height =" 418 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U953D14-7.png "alt =" clip_image008 "title =" clip_image008 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

9) "script component"

The "script component" is used to process complicated business processes. For example, a field in this solution needs to be auto-incrementing, so you need to write a script.

Set input Columns

Design script:

ScriptMain:

'Microsoft SQL Server Integration Services user script component

'This is your new script component in Microsoft Visual Basic. NET

'Scriptmain is the entrypoint class for script components

Imports System

Imports System. Data

Imports System. Math

Imports Microsoft. SqlServer. Dts. Pipeline. Wrapper

Imports Microsoft. SqlServer. Dts. Runtime. Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim intCounter As Integer = 1' Set intial value here

Public Overrides Sub input 0_ProcessInputRow (ByVal Row As input 0 Buffer)

Row. Taxis = intCounter

IntCounter = intCounter + 1 'set the incremental value here

End Sub

End Class

BufferWrapper

'This is auto-generated code that will be overwritten! Do not edit!

'Microsoft SQL Server Integration Services buffer wrappers

'This module defines classes for accessing data flow buffers

'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 Class input 0 Buffer

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 Class

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 class for your component

'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 Class UserComponent

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 inputtid = 1572 Then

Input 0_ProcessInput (New input 0 Buffer (Buffer, GetColumnIndexes (InputID )))

End If

End Sub

Public Overridable Sub input 0_ProcessInput (ByVal Buffer As input 0 Buffer)

While Buffer. NextRow ()

Input 0_ProcessInputRow (Buffer)

End While

End Sub

Public Overridable Sub input 0_ProcessInputRow (ByVal Row As input 0 Buffer)

End Sub

End Class

Public Class Connections

Dim ParentComponent As ScriptComponent

Public Sub New (ByVal Component As ScriptComponent)

ParentComponent = Component

End Sub

End Class

Public Class Variables

Dim ParentComponent As ScriptComponent

Public Sub New (ByVal Component As ScriptComponent)

ParentComponent = Component

End Sub

End Class

10) Open the "target" Data Stream

Create a ing

650) this. width = 650; "height =" 645 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U9532619-8.gif "alt =" clip_image009 "title =" clip_image009 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

11) set the ETL package. Otherwise, an error may occur when a JOB is added.

"Project Properties"-"debugging"-"debugging options"-Run64BitRuntime = false (explained later)

-EncryptSensitiveWithPassword

650) this. width = 650; "height =" 318 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U95360P-9.png "alt =" clip_image010 "title =" clip_image010 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

12) run the package directly and check the result. There is no error. After the package is successfully executed, add it to the JOB of MSSQL.

Open SQL Server Management Studio, enable SQL SERVER proxy, and create a job. For details about how to create a job, go to the official website to check the document. Here, we only focus on the key steps:

650) this. width = 650; "height =" 398 "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/1U9536405-10.png "alt =" clip_image011 "title =" clip_image011 "style =" border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px "/>

In the command window, enter:

Let the dtexec.exe application call the newly created ETL. DTSX package, And/DECRYPT 123456 is the password of the package.

Finally, explain Run64BitRuntime = false in 11). In this way, the package is processed as 32 bits. Officially, we can only create a 32-bit ETL. DTSX package.

Official statement:

In addition, some ole db providers and some ADO. NET providers are not available in 64-bit versions. If these providers are used in the package, the 64-bit SQL Server Agent may encounter connection problems when running the package using the 64-bit DTExec.exe utility.

Then, the 64-bit SQL Server Agent must run the package by using the 32-bit DTExec.exe utility.

Official understanding, scheduling 64-bit SQL Server Proxy: http://support.microsoft.com/kb/934653/zh-cn

This article is from the "Haibin technology blog". For more information, contact the author!