How to Use SSIS to Transfer Data from MS SQL Server to Oracle

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

An example of a control file is given in script 2.




(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
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() & "|")
sTemp = sb.ToString()
sw.WriteLine(sTemp.Substring(0, sTemp.Length - 1))
sb = sb.Remove(0, sTemp.Length)
iImportRecordCount = iImportRecordCount + 1
End While
Rdr = Nothing
Catch e1 As Exception
Return e1.Source & " - " & e1.Message
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
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
End Sub
Private Sub ProcessEnd(ByRef proc As Process, ByVal iMSwait As Integer)
Dim i As Integer
For i = 0 To 1 Step 0
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.

3 0
Article Categories:

Leave a Reply

Your email address will not be published. Required fields are marked *