Pada project saya yang lalu, saya diberi tugas untuk melakukan proses replikasi data dari SQL Server ke Oracle dengan menggunakan SSIS. Ini adalah project BI pertama yang saya kerjakan dan pengerjaanya memakan waktu yang lebih lama dari yang diperkirakan, kalau tidak ingin disebut sangat-sangat telat karena saya hanya diberi waktu tiga bulan sementara saya menyelesaikannya hampir 10 bulan (kebayang gak sih gimana marahnya bos dan client saya?)
What took me so long?
Yang pertama adalah karena saya tidak boleh menggunakan Replication yang terdapat di SQL Server karena client saya tersebut sudah memiliki Replication antara SQL Server dan SQL Server dan mereka tidak ingin proses Replication mereka menjadi terganggu.
Kemudian terdapat incompatibility connectivity antara SQL Server dan Oracle. Jadi, pada saat saya menggunakan OLE DB provider Oracle untuk machine ia64 dan mencoba menggunakannya untuk melakukan query data dari SSIS atau SQL Server via Linked Server terdapat masalah pada saat me-return column-column dengan tipe data numeric/decimal. Workaround paling mudah adalah melakukan konversi ke char pada saat query ke oracle, lalu mengkonversi nya ke numeric dari SQL Server. Tapi saya tidak mau menggunakan cara ini karena membuat proses query menjadi lebih lama.
Itu pada saat query, pada saat melakukan Insert, Update, dan Delete dari SSIS persoalan yang lain muncul. SSIS membutuhkan waktu yang sangat lama untuk melakukan transaksi. Sebagai ilustrasi, dibutuhkan waktu 30 menit untuk proses insert 100 ribu data sementara data yang harus di-insert jumlahnya jutaan. Berapa lama waktu eksekusi yang dibutuhkan?
The Workaround
Untungnya, saya dikasih dua buah box, yang satu x86 dan satu lagi ia64. jadilah saya pake dulu machine x86 untuk melakukan replikasi dan membuat senang bapak2 bos dan bapak2 client sambil berjanji dalam hati bahwa suatu hari nanti saya akan memindahkanya ke machine ia64.
The Conquering
Setelah tiga bulan melakukan research besar-besaran (yang terbesar yang pernah saya lakukan seumur hidup) dan tidak berhasil menemukan solusinya di internet, akhirnya titik terang muncul setelah saya berdiskusi dengan teman saya (a credit to iqbal, thanks yah bos) dan menyambangi beberapa site (yang bookmark nya hilang sekarang. huhuhu. Ternyata di luar sana juga banyak orang yang mengalami hal yang sama dengan saya).
Jadi Oracle punya yang namanya Oracle Data Acces Component (ODAC) yang biasa digunakan untuk melakukan koneksi dari aplikasi yang dibuat dengan visual studio .net ke oracle, dan thanks god ada ODAC untuk IA64.
Agar saya dapat meng-query data ke oracle dari SSIS, saya membuat sebuah custom component yang fungsinya sepert OLE DB Source. Provider nya menggunakan ODAC dan query nya seperti query biasa di oracle tanpa perlu melakukan konversi tipe data seperti menggunakan to_char.
Untuk melakukan insert/update/delete, ternyata SSIS melakukan proses insert-commit untuk setiap row yang di-insert pada non Microsoft database. Pantas saja dibutuhkan setengah jam untuk 100 ribu data. Untuk menaklukannya, saya membuat component lain (dua buah untuk insert dan update, karena requirement nya adalah mengubah flag untuk data yang di-delete dari aktif menjadi tidak aktif). Lalu untuk setiap buffer di SSIS saya menggunakan transaction, jadi saya menambahkan statement begin transaction di awal buffer lalu meng-commit transaction di akhir buffer.
Selain itu juga saya membuat custom component di control flow yang fungsinya seperti Execute SQL Task-nya SSIS karena ternyata Execute SQL Task tidak dapat digunakan untuk provider ODAC.
Dengan bantuan ODAC dan custom component yang dapat kita buat menggunakan Visual Studio .net (C# atau VB) plus SQL Server SDK akhirnya project nya dapat selesai dengan cukup baik dan memuaskan (bagi saya pribadi dan bukan buat bos/client saya tentunya mungkin yah). Selain itu, proses eksekusi SSIS juga menjadi lebih cepat jika dibandingkan dengan menggunakan component bawaan SSIS.