Data Transformation Services (DTS) pada Microsoft SQL Server 2000
Data Transformation Service(DTS) dalam SQL Server 2000 menyediakan kumpulan graphical tools dan objek pemrograman untuk menolong administrator dan developers mengatasi masalah perpindahan data, termasuk melakukan ekstrak, transformasi dan penggabungan data dari sumber data asal ke sumber data tujuan yang berbeda untuk mengembangkan aplikasi dan melakukan upgrade pada database atau server. Artikel ini memberikan pengertian, komponen-komponen dan fungsi-fungsi yang dapat diberikan oleh DTS, ilustrasi-ilustrasi gambar mengenai kegunaan dan implementasi DTS designer serta memperkenalkan DTS application development.
Pendahuluan
Permindahan data dari satu lokasi penyimpanan ke tempat penyimpanan data yang lain sering dilakukan untuk mendukung pengambilan keputusan, meningkatkan performance system, dan upgrade system yang sudah ada. Dengan Microsoft SQL Server 2000 Data Transformation Services (DTS) user dapat melakukan ekstrak, transformasi dan penggabungan data dari sumber data asal ke sumber data tujuan. Dengan tools ini user dapat menciptakan solusi masalah perpindahan data yang umum untuk keperluan khusus organisasi seperti contoh-contoh kasus berikut ini:
Permindahan data dari satu lokasi penyimpanan ke tempat penyimpanan data yang lain sering dilakukan untuk mendukung pengambilan keputusan, meningkatkan performance system, dan upgrade system yang sudah ada. Dengan Microsoft SQL Server 2000 Data Transformation Services (DTS) user dapat melakukan ekstrak, transformasi dan penggabungan data dari sumber data asal ke sumber data tujuan. Dengan tools ini user dapat menciptakan solusi masalah perpindahan data yang umum untuk keperluan khusus organisasi seperti contoh-contoh kasus berikut ini:
- Versi baru anda membutuhkan SQL Server 2000 dan mengharuskan anda untuk mengganti skema database dan merubah beberapa jenis data lama dari versi lama SQL Server atau Microsoft Access. Untuk kepentingan mengcopy dan perpindahan data, anda dapat membangun sebuah DTS solution yang dapat menyalin objek database dari sumber data asal kedalam database SQL Server 2000, dan pada waktu yang sama membuat kolom baru dan merubah tipe data.
- Untuk kepentingan penggabungan data pada Microsoft Excel spreadsheet kedalam sebuah database SQL Server, anda dapat membangun sebuah DTS solution yang bekerja pada saat sebuah pesan dikirimkan dalam antrian. Message Triggers DTS yang berfungsi untuk melakukan ekstrak data dari spreadsheet yang akan melakukan transformasi sesuai dengan yang didefinisikan, dan meload data kedalam sebuah database SQL Server.
- Data warehouse anda berisi historical data tentang operasi bisnis anda, dan anda menggunakan Microsoft SQL Server 2000 Analysis Service untuk melakukan summarize data. Data warehouse anda perlu untuk diupdate setiap saat dari Online Transaction Processing (OLTP) database. Sistem OLTP anda harus bekerja 24 jam sehari dan memiliki performance yang critical. Untuk kasus ini anda dapat membangun sebuah solusi DTS yang menggunakan file transfer protocol(FTP) untuk memindahkan data kedalam local drive, meload data ke dalam table, dan menggumpulkan data menggunakan Analysis Service. DTS juga dapat dijadwalkan untuk dioperasikan setiap saat dan DTS Logging option dapat digunakan untuk mengetahui berapa lama proses berlangsung sehingga memungkinkan anda untuk menganalisa performance.
Data Transformation Service
Selain berfungsi untuk melakukan import, export dan transport data yang beragam antara satu atau lebih sumber data, seperti misalnya SQL Server, Microsoft Excel atau Microsoft Access, DTS juga menyediakan hubungan berupa ODBC (Open Database Connectivity) data source yang disupport oleh OLE DB Provider for ODBC dan service seperti logging package execution details, controlling transactions dan penanganan variable global.
Berikut Tool-tool yang tersedia untuk menciptakan dan mengeksekusi DTS packages:
Berikut Tool-tool yang tersedia untuk menciptakan dan mengeksekusi DTS packages:
- Import/Export Wizard untuk membangun DTS packages sederhana dan mensupport perpindahan data dan transformasi sederhana.
- DTS Designer mengimplementasikan DTS object model graphically yang memungkinkan user untuk menciptakan DTS packages dengan fungsi luas.
- DTSRun merupakan command prompt utility yang digunakan untuk mengeksekusi DTS packages yang ada.
- DTSRunUI merupakan grafik interface untuk DTSRun, yang juga mengijinkan pemakaian variable global dan command lines.
- SQLAgent, bukan merupakan aplikasi DTS; meskipun demikian dapat juga digunakan oleh DTS untuk menjadwalkan pengeksekusian dari package.
Feature-feature baru yang disediakan oleh Microsoft SQL Server 2000 antara lain:
- DTS tasks baru yang termasuk dalam FTP Task, Execute Package Task, Dynamic Properties Task dan Message Queue Task.
- Enhanced logging menyimpan informasi untuk setiap package execution, memungkinkan pemeliharan execution history yang lengkap dan view information untuk setiap proses pekerjaan. Anda dapat menyimpan DTS packages seperti file-file Microsoft Visual Basic.
- Sebuah multiphase data pump baru yang memungkinkan advanced user untuk melakukan operasi customize data transformasi pada various stages.
- Parameterised source queries dalam DTS transformation task dan Execute SQL task.
- Execute Package task untuk mendaftarkan nilai variabel global secara dinamik dari parent package untuk child package.
DTS Designer yang tersedia berguna untuk:
- Menciptakan sebuah package sederhana yang berisi satu langkah atau lebih.
- Menciptakan sebuah package yang berisi workflow kompleks yang berisi langkah-langkah yang penggunaan logika kondisi, event-driven code atau multiple connections untuk sumber data.
- Mengedit package yang sudah ada.
DTS Designer interface sendiri berisi area pekerjaan yang membuat package, toolbar yang berisi package element yang dapat didrag kedalam design sheet, dan menu-menu yang berisi workflow dan perintah pengaturan package.
Dengan melakukan drag connections dan task kedalam design sheet, dan melakukan spesifikasi eksekusi order dengan workflows, user dapat dengan mudah membangun powerful DTS packages menggunakan DTS Designer.
Task untuk mendefinisikan langkah-langkah dalam sebuah Package
Sebuah DTS package biasanya terdiri atas satu task atau lebih. Setiap task mendefinisikan jenis pekerjaan yang akan diberikan selama eksekusi package. Task-task tersebut dikelompokkan berdasarkan fungsinya berikut ini:
- Transformasi data
- Mengcopy dan mengatur data
- Menjalankan tasks sebagai jobs dari dalam sebuah package
Penciptaan task dalam program dan penggabungan task tersebut kedalam DTS Designer dapat dilakukan menggunakan Register Custom Task command.
Berikut merupakan DTS Package dengan 2 task: sebuah Microsoft ActiveX Script task dan sebuah Send Mail task:
Microsoft Visual Basic Scripting Edition (VBScript), Microsoft JScript, atau ActiveState ActivePerl yang dapat didownload dari http://www.activestate.com. Send email dapat mengirim sebuah pesan yang memberitahukan bahwa sebuah package telah dijalankan. Dengan catatan tidak ada order yang dikirim pada task-task ini. Ketika package dijalankan, task ActiveX Script dan Send Mail dijalankan bersama-sama.
Ketika anda mendefinisikan sejumlah task, biasanya ada sebuah order dimana task di bentuk. Task yang memiliki sebuah order menjadi sebuah langkah proses. Dalam DTS Designer user dapat memanipulasi task pada DTS Designer design sheet dan menggunakan precedence constraints untuk mengontrol barisan dimana task dieksekusi.
Type precedence constraints yang dapat digunakan dalam DTS antaral lain:
Contoh: ActiveX Script task dan Send Mail task dengan sebuah precedence constraint On Completion. Pada saat Active X Script task selesai baik dengan hasil output sukses atau gagal, Send Mail task akan mulai bekerja.
Konfigurasi terpisah dapat dilakukan untuk Send Mail tasks, satu untuk On Success constraint dan satu untuk On Failure constraint. Dua task Send Mail dapat mengirim pesan yang berbeda berdasarkan success atau failure dari ActiveX script.
Dua atau lebih precedence constraint dapat dihubungkan pada sebuah task. Contohnya Send Mail task “Admin Notification” dapat memiliki sebuah On Success constraint dari Script #1 dan sebuah On Failure constraint dari Script #2. Pada situasi ini, DTS diasumsikan sebagai sebuah hubungan logika “AND” meskipun demikian Script #1 harus sukses dijalankan dan Script #2 harus fail sebelum dikirim ke Admin Notification message.
Connection untuk mengakses dan memindahkan data
Agar eksekusi DTS tasks dalam menyalin dan memindahkan data berjalan sukses, sebuah DTS package harus mempunyai hubungan yang valid antara sumber data asal dengan sumber data tujuan dan sumber data tambahan seperti lookup table. Ketika menciptakan sebuah package, user dapat mengkonfigurasikan hubungan dengan memilih jenis hubungan dari daftar yang tersedia pada OLE DB providers dan ODBC drivers. Jenis hubungan yang tersedia antara lain:
DTS juga mengijinkan penggunaan OLE DB connection. Icon pada connection toolbar menyediakan kemudahan akses pada common connection.
Contoh: Sebuah package dengan dua connections dimana data disalin dari database access kedalam database SQL Server production.
step 1 pada package ini adalah menjalankan SQL task, yang memeriksa apakah table tujuan telah ada. Jika tidak maka table akan dibentuk. Jika operasi SQL task berhasil maka data akan dicopy ke SQL Server database pada step 2, jika gagal maka sebuah email akan dikirim pada step 3.
The Data Pump untuk transformasi data
DTS data pump merupakan DTS objek yang melakukan import, export dan transformasi data. Data pump digunakan selama eksekusi transformasi data, data driven query, dan parallel data pump task. Pekerjaan task ini menciptakan rowsets pada hubungan sumber data asal dengan sumber data tujuan, kemudian membuat sebuah data pump untuk memindahkan baris antara kedua sumber data.
Dalam contoh ilustrasi berikut sebuah Transform Data Task digunakan antara Database Access task dengan SQL Production DB task di step 2. Transform data task merupakan panah berwarna abu-abu antara kedua connections.
User dapat mengetik sebuah query kedalam properties dialog box, atau menggunakan Data Transformation Service Query Designer, sebuah tool untuk membangun query untuk DTS tasks. Berikut merupakan contoh Query Designer yang digunakan untuk membangun sebuah query yang menggabungkan 3 tabel dalam database pubs.
Transformation | Description |
Copy Column | Digunakan untuk menyalin data secara langsung dari kolom-kolom sumber data asal ke sumber data tujuan tanpa melakukan transformasi pada data yang ada. |
ActiveX Script | Digunakan untuk membangun custom transformations. Karena transformation terjadi pada a row-by-row basis, maka ActiveX script dapat mempengaruhi kecepatan eksekusi dari DTS package. |
DateTime String | Digunakan untuk merubah data atau waktu dalam kolom data asal ke dalam format yang berbeda dalam kolom tujuan. |
Lowercase String | Digunakan untuk mengubah kolom sumber data kedalam huruf kecil jika diperlukan pada tipe data tujuan. |
Uppercase String | Digunakan untuk merubah kolom data kedalam huruf besar jika perlu pada tipe data tujuan. |
Middle of String | Digunakan untuk mengekstrak substring dari kolom sumber data, mentransformasikannya dan menyalin hasilnya pada kolom tujuan. |
Trim String | Digunakan untuk menghilangkan leading, trailing, and embedded white space dari string dalam kolom asal dan menyalin hasilnya kedalam kolom tujuan. |
Read File | Digunakan untuk membuka isi sebuah file yang namanya dicantumkan dalam kolom asal dan menyalin isinya kedalam kolom tujuan. |
Write File | Digunakan untuk menyalin isi dari kolom asal ke file yang telah didefinisikan second source column. |
Cara tercepat untuk menciptakan custom tranformation anda adalah dengan menggunakan Active Template Library (ATL) custom transformation template, yang termasuk dalam contoh program SQL Server 2000 DTS.
3 log files yang digunakan selama eksekusi package yaitu: text file, source error rows file dan destination error rows file.
- General error information ditulis dalam error text file
- Jika proses transformation gagal, maka source row juga mengalami error dan row tersebut ditulis kedalam source error rows file.
- Jika proses insert gagal, maka destination row juga gagal dan row tersebut ditulis dalam destination error rows file.
Data pump mempunyai satu fase default yaitu row transformation yang aktif pada saat mengatur column-level transformation dalam transform data task, data driven query task, dan parallel data pump task user tidak memilih jenis fase. Dengan memilih multiphase data pump dalam SQL Server Enterprise Manager, anda dapat mengakses data pump pada beberapa titik selama operasi dan penambahan fungsi. Pada saat menyalin baris data dari sumber data asal ke sumber data tujuan, data pump mengikuti proses dasar seperti ilustrasi gambar berikut:
Setelah data pump selesai memproses baris data terakhir, maka task selesai dan operasi data pump diputus. Advanced users yang ingin menambahkan fungsi ke sebuah package untuk dapat mensupport data pump phase harus melakukan langkah:
- Menulis sebuah fungsi ActiveX script phase untuk setiap data pump phase untuk customize.
- Menambahkan sebuah COM object dalam Microsoft Visual C++ ® untuk mengcustomize data pump phase yang dipilih.
Pilihan yang tersedia untuk menyimpan DTS packages antara lain:
- Microsoft SQL Server.
Menyimpan packages pada SQL Server dalam jaringan, menjaga inventory dari package tersebut, serta menambah dan menghapus package versions selama package development process.
- SQL Server 2000 Meta Data Services
Jika berencana untuk melakukan track pada package version, meta data dan data lineage information.
- Structured storage file
Jika ingin menyalin, memindahkan dan mengirimkan package melalui jaringan tanpa harus menyimpan data dalam Microsoft SQL Server database.
- Microsoft Visual Basic.
Jika anda ingin memasukkannya dalam Visual Basic programs atau menggunakannya sebagai prototype untuk DTS application development.
Dengan menggunakan Ms. Visual Basic, Ms. Visual C++ atau aplikasi lainnya yang mensupport COM, user dapat mengembangkan DTS solution menggunakan fungsi-fungsi yang tidak disupport dalam graphical tools. Dukungan yang ditawarkan oleh DTS untuk developer antara lain:
· Building packages
User dapat mengembangkan package yang kompleks dan akses pada fungsi object model yang lebih luas tanpa menggunakan DTS Designer atau DTS Import/Export Wizard.
· Extending packages
User dapat menambah fungsi baru melalui construction of custom tasks dan transformation.
· Executing packages
Eksekusi dari DTS packages tidak harus dilakukan oleh tools yang tersedia, eksekusi DTS packages mungkin dilakukan programmatically dan menampilkan progress melalui COM events, mengijinkan construction of embedded atau custom DTS execution environments.
Tidak ada komentar:
Posting Komentar