Download sample code
Apabila pada tulisan sebelumnya saya menunjukkan bagaimana melakukan left join dengan menggunakan SSIS 2005, makan pada tulisan yang sekarang saya akan menggunakan SSIS 2008. Sebagai informasi, di 2008 component Lookup telah di-enhance dengan UI yang baru dan tambahan output yaitu No Match Output sebagai output row yang tidak memiliki lookup.
Buka Business Intelligence Studio (BIDS), lalu buat SSIS project baru dengan nama WithOrWithoutMergeJoin2008 dengan cara meng-klik menu File > New Project..., pilih Business Intelligence Projects dari Project Types, pilih Integration Services Project dari Templates, beri nama WithOrWithoutMergeJoin2008 pada Name lalu klik OK
Tambahkan satu OLE DB connection pada Package.dtsx, dengan cara membuka menu SSIS > New Connection..., pilih OLEDB lalu klik Add...
Klik New...
Pilih Native OLE DB\SQL Server Native Client 10.0 dari Provider, ketik localhost atau . (titik) di server name, pilih metode autentikasi yang digunakan (dalam contoh ini saya memilih Use Windows Authentication) lalu klik OK. Klik OK sekali lagi untuk menutup dialog Configure OLE DB Connection Manager
Tambahkan Data Flow Task ke dalam Control Flow
Double click Data Flow Task, tambahkan OLE DB Source
Double click OLE DB Source, pilih LocalHost pada OLE DB connection manager, pilih SQL Command pada Data access mode, lalu isi SQL Command text dengan query berikut lalu klik OK
| select 'Prod1' as ProductCode, 10 as Quantity
union all
select 'Prod3' as ProductCode, 5 as Quantity |
Tambahkan component Lookup ke dalam Data Flow Task, lalu hubungkan output OLE DB Source dengan component tersebut
Double click Lookup (perhatikan UI nya yang berbeda dengan di 2005), pada tab General pilih Redirect rows to no match output pada Specify how to handle rows with no matching entries
Pada tab Connection pilih LocalHost pada OLE DB connection Manager, pilih Use results of an SQL query lalu isi query dengan SQL Statement berikut
| select 1 as ProductID, 'Prod1' as ProductCode
union all
select 2 as ProductID, 'Prod2' as ProductCode |
Pada tab Columns, klik kanan pada area yang saya beri tandai dengan warna merah lalu pilih Edit Mappings. Berbeda dengan 2005 yang otomatis me-mapping Column dengan nama yang sama, di 2008 kita harus melakukannya secara manual
Pilih ProductCode pada Input Column, pilih ProductCode pada ProductCode lalu klik OK. Yang harus kita perhatikan adalah bahwa tipe data pada kedua column harus sama. Pada saat kita memilih Input Column, Lookup Column akan menampilkan column-column dengan tipe data yang sama dengan Input Column
Check ProductID pada Available Lookup Columns, lalu klik OK
Karena kita ingin mengisi data penjualan yang tidak memiliki ProductID dengan null, pertama-tama kita harus mengetahui dulu tipe data ProductID. Untuk melihatnya, klik kanan pada component Lookup, lalu pilih Show Advanved Editor...
Pada tab Input and Output Properties, expand Lookup Output, expand Output Columns, pilih Product ID, lalu lihat tipe data nya pada properties. Dari gambar di bawah kita dapat mengetahui bahwa tipe datanya adalah four-byte signed integer (DT_14)
Tambahkan component Derived Column pada Data Flow Task, lalu hubungkan output Lookup dengan component tersebut. Pada saat muncul dialog Input Output Selection, pilih Lookup No Match Output pada Output lalu klik OK
Double click Derived Column, tambahkan column dengan nama ProductID, NULL(DT_14) sebagai expression, lalu klik OK. NULL(DT_14) adalah sebuah expression untuk mengisi nilai null pada column dengan tipe data four-byte signed integer, tipe data yang sama dengan column ProductID yang terdapat pada component Lookup
Tambahkan component Union All, lalu hubungkan output component Lookup dan component Derived Column dengan component Union All
Klik menu SSIS > Variables. Pada Variables pane, tambahkan satu variable dengan nama Result dengan tipe data Object
Tambahkan component Recordset Destination kedalam Data Flow Task, lalu hubungkan output component Union All dengan component tersebut
Double click Recordset Destination. Pada tab Component Properties, pilih User::Result sebagai property VariableName
Pada tab Input Columns, check ProductID dan Quantity, lalu click OK
Selesai! Pada saat package di-execute, akan terlihat bahwa dari dua data penjualan yang diambil, satu data memiliki lookup (Prod1) dan satu data lainnya tidak memiliki lookup (Prod3), keduanya di-union dan menghasilkan 2 data pada saat disimpan, seperti sebuah transformasi left join dengan menggunakan Merge Join
