Anda perlu membuat paket untuk mengimpor data dari buku kerja Excel ke dalam tabel SQL Server, menggunakan Layanan Integrasi SQL Server sebagai alat. Apa yang mungkin salah? . Tujuan artikel ini adalah untuk membantu orang menghindari semua jebakan yang saya alami saat pertama kali mempelajari SSIS Show
Artikel ini menggunakan SSIS 2012, versi terbaru pada saat penulisan, tetapi Anda tidak akan melihat banyak perbedaan jika menggunakan 2008 atau 2005. Artikel ini mengasumsikan bahwa Anda menggunakan Alat Data SQL Server – Kecerdasan Bisnis dalam Visual Studio 2012 untuk membuat paket SSIS. Business Intelligence Development Studio (BIDS) digunakan hingga SQL Server 2012. Apakah Microsoft memiliki seluruh departemen yang dikhususkan untuk memikirkan nama perangkat lunak yang menyesatkan? Contoh Sederhana KamiMisalkan Anda ingin mengimpor buku kerja Excel pembelian ke dalam tabel SQL Server (Anda dapat mengunduh buku kerja ini di bagian bawah artikel) Buku kerja buku besar pembelian kami, lengkap dengan 2 baris judul yang tidak diinginkan, baris judul yang berguna, dan beberapa baris kosong yang canggung di bagian bawah Anda dapat menggunakan Excel untuk secara manual menghapus dua baris judul teratas dan dua baris kosong terbawah untuk membuat hidup lebih mudah, tetapi ini akan menjadi semacam kecurangan (dan juga tidak ada gunanya, karena aplikasi apa pun yang menghasilkan buku kerja Excel pembelian hanya akan membuat ulang yang tidak diinginkan. . Mengutip kata-kata Kayafas dalam musikal Jesus Christ Superstar. “kami membutuhkan solusi yang lebih permanen untuk masalah kami” Inilah yang kami ingin memuat tabel SQL Server terakhir kami. 5 pembelian Sebelum memulai, pastikan Anda telah menutup buku kerja Excel Anda. Jika Anda menjalankan paket SSIS untuk mengimpor data dari buku kerja Excel yang terbuka, Anda akan mendapatkan pesan kesalahan yang sangat menyesatkan Langkah 1 – Buat ProyekSebelum Anda bisa bermain-main dengan data (maaf. mengekstrak, mengubah, dan memuatnya), Anda memerlukan proyek untuk melakukannya. Masuk ke Alat Data SQL Server atau Visual Studio, lalu pilih untuk membuat proyek baru Anda mungkin dapat melewatkan langkah ini jika Anda baru saja masuk ke SSIS untuk pertama kalinya Di bagian atas kotak dialog yang muncul, pastikan Anda membuat jenis proyek yang tepat Pilih untuk membuat proyek SSIS intelijen bisnis Anda kemudian dapat memberi proyek Anda nama yang menarik (setidaknya, lebih menarik daripada Proyek Layanan Integrasi1, yang telah saya gunakan. ) Pilih nama dan lokasi untuk paket baru Anda SSIS sekarang akan membuat proyek baru, dan juga (secara default) paket baru juga, secara imajinatif disebut Paket. dtsx. Ada dua cara Anda dapat melihat bahwa inilah yang terjadi. Salah satunya adalah Anda dapat melihat paket di Solution Explorer Paket baru yang dibuat atas nama Anda (jika Anda tidak dapat melihat Solution Explorer, pilih 'View SolutionExplorer' dari menu untuk menampilkannya) Petunjuk lain bahwa SSIS telah membuat paket untuk Anda adalah paket itu menatap wajah Anda Secara default Anda ditempatkan dalam tampilan Control Flow, yang seperti diagram alur yang menunjukkan urutan di mana tugas yang Anda buat akan dijalankan Langkah 2 – Buat Koneksi ke Database SQL Server AndaSebelum Anda melanjutkan, Anda perlu memastikan bahwa Anda telah membuat koneksi ke database SQL Server Anda. Untuk melakukannya, pertama klik kanan pada bagian 'Connection Managers' dari Solution Explorer Klik kanan untuk membuat manajer koneksi baru Cara paling efisien untuk menautkan ke SQL Server menggunakan pengelola koneksi OLEDB Pilih untuk menambahkan pengelola koneksi OLEDB Sekarang klik tombol ‘Baru…‘ untuk membuat manajer koneksi baru Anda Buat manajer koneksi baru seperti di atas Pilih server Anda, metode autentikasi, dan basis data di layar berikutnya, lalu pilih 'OK' dua kali untuk melihat manajer koneksi baru Anda terdaftar Masuk akal untuk membuat pengelola koneksi ini untuk seluruh proyek, karena kemungkinan Anda akan menggunakan koneksi yang sama di paket lain dalam proyek yang sama Saya belum menunjukkan detail lebih lanjut tentang ini di sini karena dua alasan. pengaturan akan berbeda pada mesin Anda, dan siapa pun yang membaca artikel ini kemungkinan besar telah membuat koneksi berkali-kali di banyak aplikasi perangkat lunak lainnya Langkah 3 – Buat TabelAnda tidak dapat mengimpor data ke tabel yang tidak ada, jadi hal berikutnya yang akan kita lakukan adalah membuat tabel seperti di bawah ini. Kami dapat melakukan ini secara manual dalam SQL Server Management Studio, tetapi kami bertujuan untuk solusi otomatis yang dapat kami jalankan berulang kali, jadi kami akan membuat tabel sebagai bagian dari paket SSIS kami Meja kami akan terlihat seperti ini. kami akan mengimpor nama item, harga, dan jumlah, tetapi id pembelian akan dibuat secara otomatis. Adapun total di kolom E spreadsheet kami - kami hanya akan memilih untuk tidak mengimpornya, karena dapat dibuat ulang dengan mengalikan kolom Harga dan Kuantitas kapan saja Untuk membuat tabel, pertama klik dua kali (atau klik dan seret) pada tugas 'Jalankan SQL' untuk menambahkan tugas ke aliran kontrol yang akan terlihat di layar (kami ingin membuat tabel shell dalam tugas ini) Tugas ini akan menjalankan beberapa SQL untuk menghapus tabel pembelian yang ada, dan membuat yang baru Saya cenderung memberikan tugas saya nama yang panjang dan deskriptif (geek mungkin lebih suka menggunakan nama pendek yang tidak berarti. ) Eksekusi tugas SQL setelah mengganti namanya Anda juga dapat menambahkan semacam komentar ke paket menggunakan sesuatu yang disebut anotasi Anda dapat mengklik kanan untuk menambahkan anotasi ke paket Anda – mereka tampak seperti catatan tempel Lagi pula, kembali ke cerita utama, Anda sekarang dapat mengedit tugas Execute SQL Anda Cara termudah untuk mengedit tugas SSIS adalah dengan mengklik dua kali pada ikonnya, meskipun Anda juga dapat mengklik kanan pada tugas tersebut dan memilih 'Edit…' seperti di atas Di kotak dialog yang muncul, pilih untuk terhubung ke database Anda, menggunakan pengelola koneksi yang baru saja Anda buat di tingkat proyek Anda dapat menggunakan manajer koneksi tingkat proyek dalam paket apa pun Anda sekarang dapat memasukkan properti SQLStatement, menentukan SQL yang harus dijalankan SSIS untuk tugas ini. Inilah yang saya gunakan untuk artikel ini 1 2 3 4 5 6 7 8 9 10 JIKA ADA (PILIH 1 FROM information_schema.tabel di mana nama_tabel seperti ') JATUHKAN TABEL tblPurchase -- buat tabel untuk menyimpan item buku besar pembelian BUAT TABEL tblPurchase( PurchaseId int PRIMARY KEY IDENTITAS(1,1), ItemName varchar(50), Harga mengambang, Kuantitas int ) Ini pertama-tama akan menghapus tabel apa pun yang disebut tblPurchase yang sudah ada, lalu membuat tabel baru yang kosong. Kolom PurchaseId adalah identitas, yang secara otomatis akan mengambil nilai 1, 2, 3, dll. Inilah tampilan kotak dialog Execute SQL task sekarang Properti SQLStatement seperti yang muncul setelah Anda menempelkan teks Saatnya menguji apakah ini berfungsi dengan menjalankan paket tugas tunggal Anda Klik kanan pada nama paket di Solution Explorer dan pilih untuk menjalankannya seperti yang ditunjukkan di sini. SSIS akan menyimpan paket Anda secara otomatis sebelum menjalankannya Jika semuanya berjalan dengan baik, Anda harus melihat ini Centang hijau berarti semuanya berjalan dengan baik Jika paket Anda tidak berjalan saat ini, Anda mungkin mencoba menjalankannya di komputer 64-bit. Mode default di SSIS pada penginstalan SQL Server 64-bit adalah 64-bit. Dalam hal ini, Anda harus secara khusus mengubah mode untuk menjalankan sebuah paket. Saya tidak ingin mengacaukan artikel ini dengan penjelasan tentang cara melakukannya, jadi silakan merujuk ke artikel ini untuk mengetahui cara melakukannya Anda sekarang harus memiliki tabel, yang dapat Anda lihat di SQL Server Management Studio jika Anda menginginkannya Tabel tidak memiliki catatan di – belum Anda sekarang harus menghentikan paket berjalan Pilih opsi menu (atau tekan tombol di atas) untuk menghentikan paket Anda berjalan, dan ucapkan selamat tinggal pada centang hijau untuk saat ini Sekarang saatnya membuat tugas aliran data – meskipun pertama-tama kita perlu membuat koneksi Excel Langkah 4 – Buat Koneksi ExcelSebelum Anda bisa mengimpor data dari buku kerja Excel, Anda harus membuat koneksi ke buku kerja tersebut. Anda mungkin harus membuat koneksi ini di dalam paket Anda, karena kemungkinan ini akan menjadi satu kali saja (Anda tidak perlu menggunakan koneksi yang sama di paket lainnya) Klik kanan di bagian 'Connection Managers' dari paket Anda, dan pilih untuk membuat koneksi baru Perhatikan bahwa Anda juga dapat menggunakan 'Asisten Sumber' untuk melakukan ini, tetapi saya selalu suka melakukan sesuatu secara eksplisit Asisten sumber seperti yang muncul di kotak peralatan SSIS – ini bukan Voldemort para penyihir, tapi juga bukan Dumbledore Anda sekarang dapat memilih untuk membuat koneksi Excel Ada beberapa pilihan lain Jelajahi buku kerja Excel Anda dan pilihlah Biarkan opsi 'Baris pertama memiliki nama kolom' dicentang Saat Anda memilih 'OK', Anda akan melihat koneksi Excel Anda Anda dapat mengganti nama pengelola koneksi ini, tetapi kami akan membiarkannya apa adanya Langkah 5 – Buat Tugas Aliran DataSaatnya sekarang untuk memulai pekerjaan yang sebenarnya. Kami ingin menambahkan tugas aliran data ke tab aliran kontrol paket Anda. Tugas aliran data baru ini harus mengimpor data dari buku kerja Excel ke dalam tabel SQL Server (walaupun seperti yang akan kita lihat, semuanya bisa serba salah pada tahap ini) Tambahkan tugas 'Aliran Data' ke paket Anda, dan ganti namanya menjadi 'Impor data' (seperti di atas) Anda sekarang perlu menjalankan dua tugas yang ditampilkan secara berurutan; . Untuk melakukan ini, klik tugas pertama dan seret panahnya ke tugas kedua. Panah ini disebut kendala prioritas Menghubungkan tugas, sehingga mereka mengikuti satu sama lain Anda sekarang dapat mengklik dua kali pada tugas aliran data untuk mengedit apa yang dilakukannya – kami akan menghabiskan sisa artikel ini di tab aliran data SSIS Tab Aliran Data (yang masih kosong) untuk tugas Impor data Langkah 6 – Membuat Sumber ExcelData harus datang dari suatu tempat, dan dalam kasus kami ini berasal dari Excel Seret Sumber Excel dari kotak alat SSIS ke jendela aliran data kosong Anda (di sini kami juga telah menamainya) Anda sekarang dapat mengklik dua kali pada sumber ini untuk mengeditnya, dan memberi tahu SSIS dari mana ia harus mendapatkan datanya SSIS akan secara otomatis menebak manajer koneksi jika Anda hanya memiliki satu manajer koneksi Excel untuk paket/proyek ini, tetapi Anda tetap harus memilih nama lembar kerja (seperti yang ditunjukkan di atas) Ada baiknya sekarang untuk mempratinjau data Anda, dengan mengeklik tombol 'Pratinjau...' Kami memiliki masalah yang jelas dengan 2 baris pertama dan 2 baris terakhir kami, tetapi kami dapat menyelesaikannya dengan kehilangan baris mana pun yang kolom pertamanya adalah nol, yang akan segera kami lakukan menggunakan transformasi pemisahan bersyarat Sebaiknya sekarang ganti nama semua kolom, sehingga Anda tahu apa yang dirujuknya Klik pada tab 'Kolom' (seperti yang ditunjukkan di atas), lalu beri nama yang lebih baik pada kolom keluaran, seperti yang telah kita lakukan di sini Saat Anda memilih 'OK', Anda harus memiliki sumber Excel tanpa kesalahan yang ditampilkan untuk itu Sekarang untuk melakukan sesuatu dengan data ini Langkah 7 – Menghapus Data SampahHal berikutnya yang ingin kami lakukan adalah mengalihkan semua pembelian dengan nol ke… tidak ke mana-mana, sungguh. Untuk melakukannya, tambahkan transformasi pemisahan bersyarat ke aliran data Anda Tambahkan Pemisahan Bersyarat seperti di atas (di sini kami telah menamainya juga, untuk menghilangkan null), dan arahkan output (atau "jalur aliran data", jika Anda menginginkan nama yang benar secara teknis) dari kursus Excel ke dalamnya Anda sekarang dapat mengklik dua kali pada tugas 'Split Bersyarat' untuk mengonfigurasinya. Kami akan menyiapkan dua aliran darinya Data di mana kolom id adalah nol akan turun ke pipa yang disebut 'Kasus 1' (yang sebenarnya tidak akan kami sambungkan ke apa pun); Semua data lainnya akan mengalir ke pipa yang disebut 'OK Data' Berikut cara menyiapkannya Siapkan output (disebut 'Kasus 1' secara default) yang menguji kondisi bahwa kolom Id adalah null. Anda dapat menyeret fungsi ISNULL dan kolom Id ke bawah ke dalam kotak 'Kondisi' untuk menghindari keharusan mengetiknya Di bagian bawah kotak dialog ini Anda dapat mengetikkan nama untuk output default Anda Di sini kami telah memanggil keluaran default 'OK Data' Langkah 8 – Menyalurkan 'OK Data' ke dalam Tabel SQL ServerKita seharusnya sudah mendekati akhir perjalanan kita sekarang – yang perlu kita lakukan hanyalah mengirimkan data bagus ke tabel pembelian kita. Inilah cara melakukan ini Tambahkan tujuan OLE DB (seperti yang ditunjukkan di atas) – di sini kami telah mengganti namanya menjadi 'Tabel pembelian' Anda sekarang dapat menyeret panah hijau dari transformasi 'Lose the nulls' ke tujuan tabel Pembelian Saat Anda melepaskan panah, Anda akan ditanya keluaran mana yang Anda pilih. 'Kasus 1' atau 'OK Data' (dua keluaran dari pemisahan bersyarat). Pilih 'OK Data' Setelah memetakan data ke dalam tabel pembelian, sekarang saatnya untuk mengonfigurasinya. Klik dua kali pada tujuan 'Tabel pembelian' untuk mengeditnya Pertama, pilih manajer koneksi untuk digunakan (walaupun Anda mungkin tidak perlu melakukan ini, karena SSIS akan menetapkannya secara otomatis jika Anda hanya memilikinya), dan tabel yang akan ditargetkan Anda sekarang dapat memilih kolom mana dari Excel untuk dipetakan ke kolom mana dalam tabel SQL Server Berhati-hatilah – kolom Item akan segera menimbulkan masalah… Inilah yang akan Anda lihat ketika Anda memilih 'OK' Ada masalah dengan tujuan 'Tabel pembelian' Jika Anda mengarahkan mouse ke lingkaran merah, Anda akan melihat masalahnya Masalahnya adalah Excel menggunakan data Unicode, dan kami telah membuat kolom varchar di SQL Server Langkah 9 – Mengidentifikasi dan Memecahkan Masalah Konversi DataSaat Anda membuat kolom di SQL Server, Anda dapat menggunakan nvarchar atau varchar untuk string panjang variabel Tipe data varchar menggunakan setengah jumlah byte yang digunakan nvarchar, karena tidak dapat menyimpan karakter tambahan. Kami dapat menggunakan nvarchar dan menghindari masalah ini Manfaat menggunakan Unicode adalah memungkinkan Anda menyimpan karakter internasional. saat ini lebih dari 110.000 karakter berbeda dari lebih dari 100 skrip, menurut Wikipedia Namun, kami menggunakan varchar, jadi kami perlu mengonversi karakter Unicode Excel kami menjadi karakter normal. Untuk melakukan ini, kita dapat menggunakan tugas 'konversi data'. Namun, pertama-tama, kita perlu memutuskan tautan yang telah kita buat Klik kanan pada tautan antara transformasi dan tujuan dan hapus Anda sekarang dapat menambahkan tugas 'konversi data' Di sini kami telah menambahkan tugas 'Konversi Data' (ditampilkan dipilih di sebelah kiri), dan menamainya menjadi 'Ubah Unicode menjadi varchar'. Hal berikutnya adalah menyalurkan data kita ke dalamnya Pipa 'Data OK' dari transformasi pemisahan bersyarat menjadi transformasi konversi data lebih lanjut ini Anda sekarang dapat mengklik dua kali pada tugas konversi data 'Ubah Unicode menjadi varchar', dan katakan apa yang harus dilakukan Di sini kami memilih untuk membuat kolom baru bernama ItemVarchar, yang mengambil kolom Item dan mengubahnya menjadi string non-Unicode menggunakan halaman kode ANSI default Saya juga telah mengubah panjangnya menjadi 50 karakter pada saat ini. Ini berarti bahwa string yang lebih panjang dari 50 karakter akan terpotong, sehingga menimbulkan kesalahan pemotongan. Berurusan dengan ini berada di luar cakupan artikel ini – untuk saat ini cukup dicatat bahwa tidak ada deskripsi pembelian yang cukup panjang untuk contoh kita yang membuat kita khawatir Hampir sampai. Anda sekarang dapat mengambil keluaran dari tugas konversi data ini dan memasukkannya ke tujuan tabel Pembelian Kami masih mendapatkan kesalahan, karena kami belum membuat ulang pemetaan kolom untuk tujuan Anda sekarang dapat mengklik dua kali tujuan tabel Pembelian untuk mengonfigurasi pemetaan kolom Pilih untuk memetakan kolom ItemVarchar yang baru diturunkan ke kolom ItemName di tabel SQL Server Semua kesalahan Anda sekarang seharusnya sudah hilang, dan Anda dapat menjalankan paket Anda Langkah 10 – Menjalankan PaketLangkah terakhir adalah mengimpor data Anda dengan menjalankan paket Klik kanan pada paket di Solution Explorer untuk menjalankannya (berharap kami menamainya kembali ...) Seperti inilah tampilan aliran data Ya. Ini empat kutu dari SSIS Anda sekarang harus memiliki 5 pembelian di tabel tblPurchase Anda OK, akan lebih cepat untuk mengetiknya pada kesempatan ini, tetapi Anda sekarang memiliki paket yang dapat Anda jalankan setiap akhir bulan, dan yang akan berfungsi baik jika ada 5 pembelian atau 500.000 KesimpulanLayanan Integrasi hanyalah salah satu aplikasi perangkat lunak yang menyenangkan untuk digunakan. Saya harap ini mendorong Anda untuk menggunakannya untuk mengotomatiskan pemindahan data di perusahaan Anda. Tidak ada yang lebih memuaskan selain melihat tanda centang hijau muncul di samping semua tugas dalam paket Anda saat Anda menjalankannya Bagaimana menyalin data massal dari Excel ke SQL?1. Di Object Explorer, klik kanan database/tabel/tampilan yang diperlukan dan klik Ekspor Data pada menu pintasan untuk mengaktifkan wizard ekspor . Di halaman pertamanya, Ekspor format, pilih format yang disukai. MS Excel (. xls) atau MS Excel 2007 (.
Bagaimana cara menyalin dan menempelkan data ke SQL?Menggunakan SQL Server Management Studio
. Click the tab for the table with the columns you want to copy and select those columns. Dari menu Edit, klik Salin. Klik tab untuk tabel tempat Anda ingin menyalin kolom. Pilih kolom yang ingin Anda ikuti kolom yang disisipkan dan, dari menu Edit, klik Tempel. |