Bagaimana Anda memuat nama excel dan nama sheet dengan data ke sql server dalam paket ssis?

Blog ini menunjukkan cara mengulang lembar kerja di buku kerja Excel, mengimpor konten masing-masing. Teknik ini hanya akan berhasil jika semua lembar kerja dalam format yang sama

Anda dapat mengikuti blog ini dengan mengunduh dan membuka ritsleting file ini. Blog ini ditulis menggunakan SQL Server 2016 dan Excel 2016 (untuk versi perangkat lunak sebelumnya, lihat blog saya sebelumnya; atau lihat opsi pelatihan Excel kami).  

Contoh kami

Kami ingin menggabungkan data belanja tiga bulan yang terpisah ke dalam satu tabel SQL Server

Setiap bulan berisi daftar perjalanan belanja yang terpisah, tetapi (yang terpenting) mereka memiliki format yang sama

Inilah hasil akhirnya

Seperti apa jawaban akhirnya (walaupun barisnya tidak harus dalam urutan ini)

Jika Anda ingin mempelajari studi kasus di blog ini, pertama-tama jalankan skrip ini di Management Studio untuk membuat tabel yang Anda perlukan

BUAT TABEL AdoShopping(

Nama Toko nvarchar(255),

tanggal ShopDate,

Menghabiskan desimal (10,2)

)

PERGI

PILIH * DARI AdoShopping

Itu cukup tentang apa yang perlu kita lakukan. Waktunya sekarang untuk melakukannya

Prinsip yang mendasari

Kita perlu mendapatkan daftar semua nama lembar kerja di buku kerja kita

Kami tidak akan pernah melihat ini, tetapi ini adalah kumpulan nama yang perlu kami ulangi

Kami akan menyelesaikan hal di atas menggunakan Foreach ADO. Lingkaran Enumerator Rowset Skema .NET. Anda biasanya menggunakan ini untuk mengulang tabel, tampilan, atau prosedur tersimpan dalam database SQL Server, tetapi ini juga dapat digunakan - seperti yang akan kita lihat - untuk mengulang nama lembar kerja

Langkah 1 - Buat variabel untuk menampung setiap nama lembar kerja

Hal pertama yang harus dilakukan adalah membuat variabel untuk menampung setiap nama lembar kerja secara bergantian

Sangat penting bagi Anda untuk memberi nilai pada variabel ini sama dengan nama salah satu lembar kerja yang ingin Anda impor, jika tidak, Anda tidak akan bisa menjalankan tugas aliran data nanti.

Langkah 2 - Tambahkan tugas paket dasar

Buat paket yang menghapus semua baris lama dari tabel AdoShopping, kemudian memiliki loop kosong dan tugas aliran data kosong

Tugas Execute SQL akan menjalankan perintah TRUNCATE TABLE AdoShopping (jika Anda tidak tahu cara menyiapkannya, Anda mungkin membaca blog yang salah). Lihat di bawah untuk mengetahui cara mengonfigurasi tugas lainnya

Langkah 3 - Membuat koneksi untuk Foreach Loop

Klik dua kali pada tugas Foreach Loop untuk mengeditnya, dan sebutkan seperti apa loop itu

Pada tab Collection, tentukan bahwa loop ini harus berupa ADO. Enumerator Rowset Skema .NET (. )

Sekarang pilih untuk membuat koneksi baru

Pilih untuk membuat koneksi baru

Minta untuk membuat koneksi baru

Klik untuk membuat sambungan baru

Pilih penyedia yang ingin Anda gunakan

Mungkin terlihat aneh bahwa Anda menggunakan penyedia akses untuk terhubung ke lembar kerja Excel, tetapi

Sekarang ketik buku kerja Excel yang ingin Anda tautkan

Pastikan Anda menyertakan jalur file lengkap dan nama file (jelas jalur Anda akan berbeda dengan yang ditampilkan di sini)

Pada tab Semua, atur Properti yang Diperluas ke Excel 12. 0;HDR-YA;

Ketik ini dengan hati-hati

HDR=YES memberi tahu SSIS bahwa baris pertama lembar kerja Excel Anda berisi tajuk kolom, bukan data, tetapi informasi yang lebih penting adalah nomor versi Excel internal

Uji koneksi Anda

Klik tombol ini di kiri bawah untuk menguji koneksi Anda

Anda harus mendapatkan pesan ini

Ya. Atau jika Anda mendapatkan kesalahan, pastikan Anda tidak membiarkan buku kerja terbuka di Excel

Pilih OK hingga Anda keluar dari kotak dialog untuk mengonfigurasi koneksi Anda, kemudian ganti nama koneksi secara opsional

Ganti nama koneksi yang Anda buat untuk paket Anda

Langkah 4 - Mengkonfigurasi sisa Foreach Loop

Klik dua kali pada Foreach Loop Anda untuk mengeditnya, dan tentukan bahwa Anda ingin mengembalikan daftar tabel Excel

Tabel mungkin terlihat sebagai pilihan yang aneh, tetapi secara internal ini adalah cara SQL mendapatkan nama lembar kerja

Pada tab Pemetaan Variabel, pilih untuk menyimpan bit informasi kedua yang dikembalikan dalam variabel string Anda

Ketik angka 2 sebagai nomor indeks (untuk alasannya, lihat petunjuk di bawah)

Ketika Anda mendapatkan daftar tabel seperti di atas, Anda mendapatkan nama katalog tabel, nama skema tabel, nama tabel, dan tipe tabel. Semua kecuali satu dari bit informasi ini tidak berguna (dan dua yang pertama bahkan tampaknya tidak disetel). Anda ingin menyimpan bit informasi ketiga, nama tabel, yang merupakan nomor indeks 2 (karena hampir semua yang ada di SSIS diberi nomor dari 0)

Langkah 5 - Mengonfigurasi tugas aliran data

Berikut tampilan tugas aliran data

Detail tentang cara mengekspor ke SQL Server tidak ditampilkan di sini. Sumber Excel harus mengarah ke Shopping. buku kerja xlsx

Untuk sumber Excel, pilih sebagai mode akses data yang berisi nama lembar kerja dalam variabel tabel, dan tentukan yang mana

Setiap putaran, sumber Excel akan menunjuk ke lembar kerja yang namanya akan dimuat dalam variabel SheetName

Berikut adalah pemetaan untuk tujuan SQL Server

Pemetaan dari sumber Excel ke tabel tujuan SQL Server AdoShopping

Langkah 6 - menjalankan paket

Hal terakhir yang harus dilakukan adalah menjalankan paket

Apa yang harus Anda lihat dalam alur kontrol

Tugas aliran data akan berkedip sekali untuk setiap lembar kerja

Di akhir paket, inilah yang harus ditampilkan aliran data (karena ada 6 baris di lembar kerja Maret, baris terakhir yang akan diimpor)

Bagaimana cara meneruskan nama lembar Excel secara dinamis di SSIS?

Bagaimana cara mengimpor data dari tabel Excel ke SQL menggunakan SSIS?

Bagaimana cara mereferensikan spreadsheet Excel dalam SQL?

Bagaimana cara memuat banyak file Excel dengan nama sheet berbeda di SSIS?