download sample code

Beberapa hari yang lalu ada teman saya yang bertanya mengenai SSIS pada saya. Berhubung sedang banyak pekerjaan, akhirnya baru sekarang saya bisa menjawab pertanyaannya.

Teman saya itu bercerita bahwa dia mendapat tugas dari dosennya untuk membuat sebuah ETL menggunakan SSIS yang fungsinya mengisi ID pelajar dengan format 4 digit ID kota + 2 digit ID pelajar. Contohnya adalah sebagai berikut:

Dari table diatas ID Pelajar adalah kombinasi ID Kota ditambah maks ID pelajar yang terdapat di kota tersebut. Sehingga jika ada pelajar baru dengan nama E dengan ID Kota 0004, maka ETL nya harus menghasilkan ID Pelajar 000401 karena belum terdapat data pelajar di kota 0004.

Apabila kita melihat component-component standar yang terdapat pada SSIS, tentunya kita tidak akan mendapati component yang dapat menghasilkan data yang diinginkan oleh teman saya tadi. Satu-satunya pilihan adalah dengan menggunakan Script Component, yang isinya mendapatkan kombinasi ID Pelajar berdasarkan kotanya.

Logik nya sendiri cukup mudah. Pada saat preeksekusi package, kita ambil ID kota dan Max ID Pelajar yang terdapat di setiap kota lalu ditampung pada sebuah tempat. Pada saat package dieksekusi, cari ID Kota untuk setiap baris data yang ditransformasi. Apabila ID Kotanya ketemu, maka isi kolom ID Pelajar dengan ID Kota + Max ID Pelajar ditambah 1. Apabila nggak ketemu, tambahkan ID Kota dan ID Pelajar kedalam penampungan sementara.

Supaya blog nya gak terlalu panjang, saya gak akan menulis semuanya, hanya bagian Script Component nya saja. Untuk melihat keseluruhan package-nya, silakan download sample code-nya.

Menambahkan Koneksi

Untuk menambahkan koneksi ke database CampusDB, buka tab Connection Manager, klik tombol Add, lalu pilih CampusDB di column Connection Manager, dan isi Name dengan CampusDBConnection. Koneksi ini kita perlukan untuk mengambil data ID Kota dan Max ID Pelajar di tiap kota.

Menambahkan Input Column

Pilih tab Input Columns, lalu centang CityCode. Input column ini berfungsi untuk memilih column mana yang akan digunakan pada script dan mendeklarasikan penggunaannya, apakah hanya ReadOnly atau ReadWrite

Menambahkan Output Column

Untuk menambahkan column output hasil script component, pilih tab Input and Outputs, expand Output 0 > Output Columns lalu klik Add Column. Isi nama column dengan ID, tipe data string[DT_STR] dan length 6.

Script

Oke, semua yang kita butuhkan sudah siap, sekarang kita mulai the fun part-nya. Untuk mulai menggunakan script component, pilih tab Script, lalu klik tombol Edit Script

Tambahkan reference ke Microsoft.SQLServer.ManagedDTS

Tambahkan import statement (untuk VB) atau using statement (untuk C#) ke System.Data.OleDb. BTW, saya kasih contohnya dalam VB supaya yang *** C# bisa ngelatih C# nya.

Tambahkan juga dua global variable berikut

Variable _connection digunakan untuk melakukan koneksi ke database. Yang harus diperhatikan dalam mendeklarasikan tipe datanya adalah bahwa tipenya harus mengikuti tipe connection yang kita buat pada saat meng-create data source. 

_dtStudent digunakan untuk menyimpan data kota beserta max id pelajarnya. Tentu ini hanya contoh, kita bisa menggunakan tipe data lain sesuai dengan kebiasaan kita.

Melakukan koneksi

Untuk melakukan koneksi ke database, isi _connection dengan koneksi CampusDBConnection.

Mengambil ID Kota dan Max ID Pelajarnya

Pengambilan datanya harus dilakukan sebelum package dieksekusi, agar hanya sekali dilakukan dan tidak berulang-ulang untuk setiap row yang ditransformasi. Hal ini tentu dimaksudkan agar tidak terjadi roundtrip dengan server yang akan mengurangi performance dan meningkatkan traffic jaringan.

Mengisi ID Pelajar

Disinilah proses pengisian max id dimulai. Dan code saya seharusnya sudah menjelaskan semuanya, jadi saya nggak perlu cerita lagi.