Korišćenje SSIS za prebacivanje podataka sa MS SQL Server-a na Oracle

Dosta često se, u složenijim informacionim sistemima, javlja potreba za presipanjem podataka sa MS SQL Server-a na Oracle. Većina MS SQL Server orijentisanih developera će reći: Rešenje je naravno SSIS. Ok, ali da li je to baš tako jednostavno?

U načelu jeste jednostavno kreirati SSIS paket koji će sa MS SQL Server-a presipati podatke na Oracle. Za početak je neophodno na razvojnoj mašini imati instaliranog Oracle klijenta sa definisanom konekcijom, u faju tnsnames.ora, prema ciljnom Oracle serveru i podrazumevane privilegije na konkretnom Oracle serveru i objektima kojima će se pristupati. Zatim je naravno potrebno imati instaliran SQL Server Business Intelligence Development Studio u verzijama SQL Server-a pre verzije 2012 ili SQL Server Data Tools u verziji MS SQL Server-a 2012.

Zatim teba kreirati SSIS projekat, u Control Flow paketa dodati Data Flow Task i u njemu kreirati potrebne OLEDB konekcije: prema izvoru MS SQL Serveru i prema cilju Oracle serveru (slika 1).

b1601

Slika 1. Definisanje OLE DB konekcije prema Oracle Serveru

Zatim treba u Data Flow Task dodati potrebne taskove za izvor (OLE DB Source task), eventualnu konverziju tipova (Data Conversion Task) i task za cilj (OLE DB Destination task) i zatim ih konfigurisati (slika 2).

b1602

Slika 2. Taskovi za prebacivanje podataka sa MS SQL Server-a na Oracle Server

Izvršavanje ovakvog SSIS paketa će raditi, naravno ako ste ga dobro konfigurisali, i omogućiće presipanje podataka sa MS SQL Server-a na Oracle Server. Gde je problem? Problem je u brzini prebacivanja podataka tačnije u brzini upisa na Oracle kroz OLE DB Destination task. Za prebacivanje podataka iz tabele sa 30-tak kolona i sa recimo 3 miliona zapisa će Vam trebati 15-tak SATI i to na nekom serverskom hardveru. Promena provajdera (umesto Microsoft OLE DB provajdera Oracle DB provajder) neće mnogo pomoći pogotovu što Oracle provajder ne podržava bulk. Neće pomoći ni prelazak sa OLDE DB na ADO. Malo će se ubrzati čitava stvar ali je i dalje beznadežno sporo. Šta uraditi?

Jedno od rešenja koja uključuju SSIS pakete je i korišćenje oracle tool-a SQL*Loader (sqlldr.exe) koji se nalazi u BIN folderu foldera u koji je instaliran Oracle klijent (recimo C:\oracle_client\product\10.2.0\client_1\BIN). SQL*Loader je alat za bulk insert podataka u Oracle tabele iz tekstualnog fajla. SQL*Loader Command Line Reference se mogu naći na:

http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_params.htm#g1014550.

Primer korišćenja SQL*Loader-a iz Command Prompt-a je dat u skriptu 1:

sqlldr userid=user/password, control=oraldr.ctl, data=data4ora.txt, log=ldr.log, PARALLEL=true

Skript 1. Primer korišćenja SQL*Loader-a

Parametar control se odnosi na control fajl (recimo sa imenom oraldr.ctl) u kome se definiše insert podataka u ciljnu tabelu na Oracle-u. SQL*Loader Control File Reference se mogu naći na: http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch05.htm. Primer control fajla dat je u skriptu 2.

                                            LOAD DATA CHARACTERSET UTF8

                                            APPEND INTO TABLE SEMA.TABELA

                                            FIELDS TERMINATED BY ‘|’

                                            (COLL_1, COLL_2, COLL_3, COLL_4, COLL_5,…)

Skript 2. Primer control fajla

Da bi se automatizovalo korišćenje SQL*Loader-a iz SSIS paketa prvo je potrebno dodati jedan Script Task na Control Flow SSIS paketa (slika 3).

b1603

Slika 3. Script Task u SSIS paketima

Zatim je potrebno definisati logiku Skript Task-a kojom će se kreirati tekstualni fajl sa podacima koji će biti dobijeni sa SQL Server-a. Pimer VB.NET koda za kreiranje tekstualnog fajla sa podacima dat je u skriptu 3.

Dim sw As New StreamWriter(sPath & "data4ora.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

Skript 3. Kod za kreiranje tekstualnog fajla sa podacima sa MS SQL Server-a

Posle kreiranja tekstualnog fajla sa podacima sa SQL Server-a potrebno je kreirati proces kojim će se izvršiti sqlldr.exe (SQL*Loader) sa neophodnim parametrima ili prethodno pripremljeni .bat fajl sa pozivom sqlldr.exe. Primer VB.NET koda je dat u skriptu 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

Skript 4. Pokretanje .bat fajla u zasebnom procesu

Ovakav SSIS Skript Task će posao prebacivanja podataka sa SQL Server-a iz tabele sa 30-tak kolona i sa recimo 3 miliona zapisa na Oracle (primer sa početka teksta) svesti sa 15-tak sati na 15-tak minuta u zavisnosti od serverskog hardver-a i parametara sa kojima se poziva SQL*Loader (sqlldr.exe). Parametri sa kojima se vredi poigrati su BINDSIZE, READSIZE i ROWS.

Probajte ako do sad niste. Radi posao.

Leave a Reply

%d bloggers like this: