Jobsheet-2 Pengenalan SSIS & SQL Server Agent [PDF]

Citation preview

Jurusan Teknologi Informasi Politeknik Negeri Malang Jobsheet Minggu-4: Pengenalan SSIS & SQL Server Agent Mata Kuliah Data Warehouse Penulis: Yoppy Yunhasanwa September 2019



Topik 1. 2. 3. 4.



Load Data dengan Export/Import Wizard Membuat Package SSIS dengan Export/Import Wizard Menjalankan Package SSIS dengan SQL Server Agent Menjalankan Package SSIS dengan SSDT



Tujuan 1. 2. 3. 4. 5.



Mahasiswa memahami konsep ETL Mahasiswa memahami konsep package SSIS Mahasiswa mampu membuat package SSIS dengan Export/Import Wizard Mahasiswa mampu menjalankan package SSIS dengan SQL Server Agent Mahasiswa mampu menjalankan package SSIS dengan SSDT



Pendahuluan ETL merupakan kunci utama dari keberhasilan pembangunan sebuah Data Warehouse. ETL terdiri dari 3 proses utama yang berjalan secara berurutan: 1. Extract Proses ekstraksi atau pengambilan data dari data source. 2. Transform Proses penyesuaian format data yang diambil dari data source, agar sesuai dengan format data yang ada pada Data Warehouse. 3. Load Adalah proses pengisian data yang sudah disesuaikan formatnya tadi ke dalam tabel-tabel yang ada pada database data warehouse.



Untuk mengimplementasikan sebuah engine ETL, kita dapat melakukannya sendiri dengan coding dari awal, atau dengan menggunakan bantuan framework/tools yang banyak tersedia seperti SSDT. SSDT adalah tool 1



yang dibuat oleh Microsoft untuk merancang sebuah alur logika dari ETL. File yang dihasilkan nantinya adalah file yang berekstensi *.dtsx, dimana file tersebut nantinya bisa dieksekusi secara otomatis dan periodic oleh SQL Server Agent. Pada praktikum ini kita akan berkenalan dengan SQL Server Agent, SSDT, dan SSIS Packages. Sekedar informasi, membuat logika ETL dengan tools memang sangatlah jauh lebih mudah, tetapi perlu diingat bahwasannya sebagian besar tools yang digunakan untuk membuat logika ETL di pasaran tidaklah gratis. Oleh karenanya kita juga perlu untuk mampu membuat ETL from scratch, yaitu membuat sendiri dari awal tanpa menggunakan tools. Praktikum – Bagian 1: Menyiapkan Sample Data Source dan Skema Data Warehouse Untuk dapat memahami konsep ETL, mula-mula kita harus memiliki sample data sederhana terlebih dahulu, dimana dengan mengamati database tersebut, kita dapat membayangkan proses perpindahan data dari data source menuju data warehouse. Ikuti langkah berikut untuk melakukan setup 2 database tersebut.. Langkah Keterangan Clone atau unduh file database dari repository GitHub berikut: https://github.com/yunhasnawa/Toko-Barokah-Sample-DW Pastikan file yang diunduh setidaknya terdapat: 1. File berekstensi BAK dengan nama TokoBarokah.7z. 2. File SQL dengan nama SetupTokoBarokahDW.sql 3. File CSV dengan nama ExportBarang.csv



1



2



Pertama-tama extract-lah file TokoBarokah.7z sehingga menjadi file TokoBarokah.bak yang berukuran sekitar 150an MB. Kemudian restore-lah file TokoBarokah.bak ke server Anda dengan bantuan SSMS. Pastikan pada server database Anda menjadi terdapat sebuah database baru bernama TokoBarokah. Database ini berisi beberapa tabel dengan sejumlah cukup banyak baris data. 2



Pada repository yang sama telah disediakan juga file SQL bernama SetupTokoBarokahDW.sql buka dan jalankan file tersebut di jendela Query Editor SSMS Anda. 3



Pastikan sekarang terdapat database bernama TokoBarokahDW pada SQL Server Anda. Perhatikan pada database tersebut terdapat 1 buah tabel fakta dan 1 buah tabel dimensi. Pada praktikum bagian selanjutnya, kita akan melakukan Load data dari file CSV ke tabel DimBarang.



4



Praktikum – Bagian 1: Membuat Package SSIS dengan Export/Import Wizard Salah satu proses utama yang paling penting pada ETL adalah proses Export/Import data. Hal ini lumrah mengingat pada ETL proses diawali dengan proses Extract data yang apabila dilihat dari sisi Data Source, tidak lain merupakan proses Export. Demikian juga pada akhirnya, ETL akan ditutup dengan proses Load yang jika dilihat dari sisi Data Warehouse tidak bukan adalah proses Import. 3



Pada praktikum bagian ini kita akan mencoba melakukan impor data ke tabel yang ada di data warehouse dengan sumber data berupa sebuah file CSV. File CSV adalah salah satu format yang paling lumrah digunakan sebagai sumber data yang akan diambil ke data warehouse. Langkah Keterangan Untuk mengimpor data dengan Export/Import Wizard, pertama-tama lakukan klik kanan pada nama database kemudian pilih Tasks lalu Import Data…



1



Akan muncul jendela selamat datang, klik saja next hingga muncul jendela Choose a Data Source. Pada jendela ini pastikan di kolom Data source: isinya adalah Flat File Source karena kita akan mengimpor data dari file CSV. Klik Browse untuk menyesuaikan letak file CSV-nya dimana. Pastikan isiannya seperti pada gambar berikut, kemudian klik Next >.



2



4



Pada tampilan yang muncul berikutnya, Anda dapat memastikan bahwa data yang ada pada file CSV yang akan diimpor terlihat disini. Apabila belum muncul, maka berarti ada pengaturan yang masih belum benar. Jika data sudah terlihat disini, klik Next.



3



4



Berikutnya akan muncul jendela dialog Choose a Destination. Pada kolom isian Destination, isikan connection provider yang tepat yaitu SQL Server Native Client 11.0. Connection Provider ini digunakan karena data yang diimpor akan ditulis ke DBMS SQL Server. Apabila datanya akan diisikan ke DBMS lain, maka kita dapat memilih connection provider yang sesuai. Pastikan database yang terpilih adalah TokoBarokahDW, lalu klik Next.



5



Di tampilan yang muncul berikutnya, pastikan di kolom Destination:, yang dipilih adalah tabel [dbo].[DimBarang], selanjutnya klik Edit Mappings…



5



Di jendela Column Mappings, pastikan semua tipe data sesuai dengan yang ada pada gambar di bawah (semuanya varchar). Jika sudah sesuai, klik OK. Lalu di dialog yang sebelumnya, klik Next.



6



7



Akan muncul dialog Save and Run Package. Pada bagian ini, pastikan opsi Save SSIS Package sudah tercentang, demikian pula pada opsi File System. Klik Next. 6



Di dialog yang muncul berikutnya isikan nama file package-nya dengan nama LoadDimBarang. Klik Browse… dan simpan package tersebut di folder yang mudah Anda ingat. Perhatikan ekstensi nama file adalah *.dtsx, klik Next.



8



7



Dialog yang muncul berikutnya adalah dialog berisi ringkasan proses impor yang akan dilakukan. Pastikan pada dialog Anda muncul seperti di gambar berikut lalu klik Finish.



9



Proses impor akan berjalan. Apabila langkah-langkah sebelumnya dilakukan dengan benar, maka proses impor akan berstatus Success. Klik Close.



10



8



Ketikkan query berikut untuk menampilkan data yang ada pada tabel DimBarang, apabila impor berhasil, maka akan muncul 1000 baris baru pada tabel tersebut sebagaimana terlihat pada gambar berikut.



11



Praktikum – Bagian 2: Menjalankan Package SSIS dengan SQL Server Agent Setelah package SSIS dibuat, maka untuk menjalankannya, dapat dilakukan dengan 2 cara. Yang pertama adalah dengan menggunakan SQL Server Agent. Dan cara lainnya dalah dengan menggunakan IDE Microsoft SSDT. Pada praktikum bagian ini, kita akan menjalankan package yang telah kita buat sebelumnya, dengan menggunakan SQL Server Agent. Untuk melakukannya, ikuti langkah-langkah berikut! Langkah Keterangan Pada bagian Server Explorer di sebelah kiri layar, pastikan disana terdarapat service SQL Server Agent. Apabila tidak ada, maka periksa kembali instalasi anda. Pastikan SQL Server yang Anda gunakan adalah SQL Server 2017 Developer Edition atau jika Anda menggunakan versi-versi dibawahnya, gunakan versi Enterprise Edition. Expand-lah menu SQL Server Agent, akan terlihat beberapa folder. Pada folder Jobs, klik kanan lalu klik New Job…



1



2



Akan muncu jendela New Job. Pada jendela tersebut, isikan data di kolom Name, Category, dan Description. Sesuaikan isian-isiannya seperti pada gambar berikut, lalu klik OK.



9



Kemudian di kolom Select a page, klik menu Steps. Lalu klik New… Steps, adalah langkah-langkah yang dilakukan dalam suatu Job. Pada suatu step, kita dapat melakukan berbagai macam proses salah satunya adalah menjalankan SSIS Packages.



3



10



4



Akan muncul jendela baru untuk membuat Step. Pada bagian ini, isikan di bagian Step name: nama yang sesuai. Kemudian pada Type: pilih SQL Server Integration Service Package. Pada bagian Package Source pilih File System. Lalu arahkan ke tempat dimana anda menyimpan file DTSX Anda sebelumnya (Praktikum Bagian 1). Setelah itu klik OK.



Hapuslah terlebih dahulu semua data yang ada pada tabel DimBarang. Pastikan tabel tersebut benar-benar kosong! Untuk menjalankan package melalui SQL Server Agent, maka klik kana Job yang sudah Anda buat tadi di folder SQL Server Agent → Jobs, lalu klik pilihan Run Step….



5



6



Job akan dijalankan, ini berarti package SSIS yang Anda buat tadi akan dieksekusi. Apabila berhasil, lakukan SELECT kembali ke tabel DimBarang. Maka tabel yang sebelumnya isinya sudah Anda hapus tadi, akan kembali berisi 1000 baris data dari file CSV yang sama.



11



Praktikum – Bagian 3: Menjalankan Package SSIS dengan MS SSDT SSDT adalah sebuah IDE buatan Microsof yang berguna untuk membuat package SSIS. Kepanjangan dari SSDT adalah SQL Server Data Tools. Dengan SSDT ini, kita dapat membuat package-package SSIS yang kompleks, seperti mengambil data dari beberapa data source. Dimana hal tersebut tidak dapat dilakukan oleh Export/Import Wizard. Pada bagian ini kita akan berkenalan dengan MS SSDT diawali dengan mengeksekusi package yang sama dengan di praktikum bagian sebelumnya. Untuk melakukannya, ikuti langkah-langkah berikut. Langkah Keterangan Bukalah aplikasi MS SSDT dengan mencarinya di start menu atau mengklik ikon SSDT di desktop PC Anda. Tampilan awal SSDT kurang lebih seperti pada gambar berikut.



1



2



Cobalah untuk membuat project baru dengan cara mengklik File kemudian pilih New → Project.



12



Pada jendela New Project yang muncul, pilihlah Business Intelligence di bagian Installed → Templates. Pilih jenis project Integration Service Project. Beri nama project tersebut, lalu klik OK.



3



4



Sejurus kemudian, tampilan akan menjadi seperti pada gambar berikut. Pada tampilan tersebut, perhatikan di panel Solution Explorer di sebelah kanan layar, pada bagian SSIS Packages otomatis dibuatkan sebuah package baru bernama Package.dtsx, dimana package tersebut adalah package kosong. Klik tombol Run (segitiga warna hijau) dibagian atas jendela SSDT dan perhatikan apa yang terjadi! Package kosong tersebut akan dieksekusi dengan sukses.



13



Walaupun saat ini package kosong dapat dieksekusi, tetapi tidak terjadi apapun karena dalam package tersebut masih belum terdapat logika ETL apapun. Sekarang coba kita lihat bagaimana isi dari package yang sebelumnya dibuat dengan Export/Import Wizard. Untuk melakukannya, klik kanan pada folder SSIS Packages yang ada di Solution Explorer. Pilih menu Add Existing Package.



5



6



Pada jendela yang muncul berikutnya, pada isian Package location, pilih File System. Lalu pada bagian Package Path pilih lokasi tempat Anda menyimpan package SSIS yang sebelumya tadi sudah dibuat. Klik OK.



14



Klik 2x package LoadDimBarang.dtsx yang baru saja ditambahkan tadi, maka akan dimunculkan tampilan seperti pada gambar berikut. Pada tampilan tersebut terdapat tab Control Flow, Data Flow, dan 3 tab lainnya. Sementara ini fokuslah terlebih dahulu ke dua buah tab tersebut. Control Flow adalah tempat dimana kita merancang control atau logika package secara global. Pencabangan, perulangan, kita lakukan di tab ini.



7



8



Klik 2x Data Flow Task 1, maka akan dibukakan tab disebelah tab Control Flow yaitu tab Data Dlow. 15



Data Flow adalah tempat dimana kita merancang pergerakan data. Darimana asalnya, akan diformat seperti apa, dan akan dimasukkan kemana. Disini dapat dilihat bahwa alur pergerakan datanya masih sangat sederhana dimana hanya terdiri dari 1 data source dan 1 destination, tanpa ada proses format data apapun. Disinilah keterbatasan Export/Import Wizard dimana ia hanya mampu menggenerate package SSIS dengan alur data dan logika kontrol yang sederhana.



Sekarang, kosongkan kembali tabel DimBarang. Lalu klik tombol Run (segitiga warna hijau) untuk menjalankan pacakage tersebut. Perhatikan eksekusi akan berjalan dengan sukses.



9



16



Periksa kembali tabel DimBarang yang tadinya kosong, lagi-lagi sekarang terisi kembali data yang sama sebanyak 1000 record.



10



Tugas Latihan 1. Ekspor data dari tabel dbo.Karyawan di database TokoBarokah ke dalam file CSV. Anda boleh menggunakan syntax T-SQL atau Export/Import Wizard. 2. Buatlah package SSIS untuk mengimpor data file CSV pada soal nomor 1 ke tabel DimKaryawan di database TokoBarokahDW! 3. Eksekusilah package pada soal nomor 2 pada SSDT dan tunjukkan hasil eksekusinya. 4. Dokumentasikan langkah-langkah pada tugas nomor 1, 2 dan 3 ke sebuah laporan dengan format PDF. Kumpulkan ke masing-masing dosen pengampu! -- Selamat Mengerjakan --



17