Bagaimana cara mengimpor data dari excel ke sql menggunakan ssis?

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

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 Kami

Misalkan 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 Proyek

Sebelum 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 Anda

Sebelum 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 Tabel

Anda 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 Excel

Sebelum 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 Data

Saatnya 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 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 Excel

Data 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 Sampah

Hal 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 Server

Kita 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 Data

Saat 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 Paket

Langkah 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

Kesimpulan

Layanan 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 cara mengimpor data dari Excel ke SQL?

Bagaimana cara mengimpor file CSV ke SQL Server menggunakan SSIS?

Bagaimana cara menanyakan spreadsheet Excel di SSIS?

Bagaimana cara mengimpor data dari Excel ke SQL Server menggunakan prosedur tersimpan?