Another very interesting post to help you learn how to migrate data from MSSQL to Oracle. Quite often, in more complex information systems, there is a need to transfer data from MS SQL Server to Oracle. Most MS SQL Server oriented developers will say: The solution is of course SSIS. Ok, but is it that simple?
Below the post, you can find some scripts that will help you migrate data from MSSQL to Oracle and speed up data transfer. Installing SSIS isn’t that difficult, but it’s important to know how to speed up data transfer. Read carefully use scripts and try it yourself.
In principle, it’s easy to create an SSIS package that will dump data to Oracle from MS SQL Server. For starters, it is necessary to have a defined connection client installed on the development machine, in the tnsnames.ora file, according to the target Oracle server, and the default privileges on the specific Oracle server and the objects to be accessed. Then, of course, you need to have SQL Server Business Intelligence Development Studio installed in versions of SQL Server before version 2012 or SQL Server Data Tools in MS SQL Server 2012 and higher.
Then create the SSIS project, add the Data Flow Task to the Control Flow package and create the necessary OLEDB connections in it: according to the source of the MS SQL Server and towards the target Oracle server (Figure 1).
Figure 1. Defining an OLE DB connection according to Oracle Server
After the first step, the necessary task for the source (OLE DB Source task), eventual conversion of types (Data Conversion Task) and task for the target (OLE DB Destination task) should be added to the “Data Flow Task” and then configured (Figure 2).
Figure 2. Tasks for transferring data from MS SQL Server to Oracle Server
Running this SSIS package will work, of course, if you have it well configured, and will allow data transfer from MS SQL Server to Oracle Server. Where’s the problem? The problem is the data transfer speed is more accurate in the Oracle write speed through the OLE DB Destination task. To transfer data from a table of about 30 columns and say 3 million records, you will need about 15 hours on some server hardware. Changing the provider (instead of the Microsoft OLE DB provider Oracle DB provider) will not help much, especially since the Oracle provider does not support bulk. Switching from OLDE DB to ADO won’t help either. It’ll speed up the whole thing a little bit, but it’s still hopelessly slow. What to do?
>One solution that includes SSIS packages is to use the oracle tool SQL * Loader (sqlldr.exe) located in the BIN folder of the folder where the Oracle client is installed (for example C: \ oracle_client \ product \ 10.2.0 \ client_1 \ BIN).
SQL * Loader is a tool for bulk insertion of data into Oracle tables from a text file. SQL * Loader Command Line References can be found at:
An example of using SQL * Loader from Command Prompt is given in script 1:
sqlldr userid=user/password, control=oraldr.ctl, data=data4ora.txt, log=ldr.log, PARALLEL=true
Script 1. Example of using SQL * Loader
The control parameter refers to a control file (say named oraldr.ctl) that defines the insertion of data into a target table in Oracle.
SQL * Loader Control File File references can be found at http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch05.htm.
An example of a control file is given in script 2.
LOAD DATA CHARACTERSET UTF8 APPEND INTO TABLE SEMA.TABELA FIELDS TERMINATED BY ‘|’ (COLL_1, COLL_2, COLL_3, COLL_4, COLL_5,…)
Script 2. Example of a control file
To automate the use of SQL * Loader from SSIS packages, one must first add one Script Task to the Control Flow SSIS package (Figure 3).
Figure 3. Script Task in SSIS packages
Then it is necessary to define the logic of the Script Task that will create a text file with the data that will be obtained from SQL Server. The VB.NET code example for creating a text data file is given in script 3.
Dim sw As New StreamWriter(sPath & "transferdata4ora.txt", False) Dim EConn As New OleDb.OleDbConnection(sConnStrSQL) Dim MyCommand As New OleDb.OleDbCommand(sQuerySQL, EConn) Dim Rdr As OleDb.OleDbDataReader Try '---------------------------------------------- EConn.Open() Rdr = MyCommand.ExecuteReader() Dim colNum As Integer = Rdr.FieldCount Dim sb As New System.Text.StringBuilder() Dim sTemp As String iImportRecordCount = 0 While Rdr.Read() '----------------------------------- For i As Integer = 0 To colNum - 1 Step 1 sb.Append(Rdr(i).ToString() & "|") Next sTemp = sb.ToString() sw.WriteLine(sTemp.Substring(0, sTemp.Length - 1)) sb = sb.Remove(0, sTemp.Length) '----------------------------------- iImportRecordCount = iImportRecordCount + 1 End While Rdr = Nothing EConn.Close() '---------------------------------------------- Catch e1 As Exception Return e1.Source & " - " & e1.Message Finally sw.Close() sw = Nothing End Try
Script 3. Code to create a text file containing data from MS SQL Server
After creating a text file containing data from SQL Server, it is necessary to create a process that will execute sqlldr.exe (SQL * Loader) with the necessary parameters or a previously prepared .bat file with the call sqlldr.exe. An example of a VB.NET code is given in script 4:
Dim proc As New Process Dim psi As New System.Diagnostics.ProcessStartInfo Try StartProces(proc, psi, sPath) ProcessEnd(proc, 1000) Catch e2 As Exception Return e2.Source & " - " & e2.Message End Try Private Sub StartProces(ByRef proc As Process, ByRef psi As ProcessStartInfo, ByVal sAppPath As String) psi.Arguments = "" psi.CreateNoWindow = True psi.WindowStyle = ProcessWindowStyle.Hidden psi.FileName = sAppPath & "\loader.bat" proc.StartInfo = psi proc.Start() End Sub Private Sub ProcessEnd(ByRef proc As Process, ByVal iMSwait As Integer) Dim i As Integer For i = 0 To 1 Step 0 System.Threading.Thread.Sleep(iMSwait) If proc.HasExited Then Exit For End If Next i End Sub
Script 4. Run a .bat file in a separate process
This kind of SSIS Script Task will take the job of moving data from SQL Server from a 30-column spreadsheet and, say, 3 million records to Oracle (beginning of text), from 15 hours to 15 minutes, depending on server hardware -a and parameters invoked by SQL * Loader (sqlldr.exe). The parameters worth playing with are BINDSIZE, READSIZE, and ROWS.
Try it if you haven’t.