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!