Mengulangi File dan Tabel Excel dengan Kontainer Perulangan Foreach
Berlaku untuk:
SQL Server SSIS Integration Runtime di Azure Data Factory
Prosedur dalam topik ini menjelaskan cara mengulang buku kerja Excel dalam folder, atau melalui tabel dalam buku kerja Excel, dengan menggunakan kontainer Foreach Loop dengan enumerator yang sesuai.
Penting
Untuk informasi mendetail tentang menyambungkan ke file Excel, dan tentang batasan dan masalah yang diketahui untuk memuat data dari atau ke file Excel, lihat Memuat data dari atau ke Excel dengan SQL Server Integration Services (SSIS).
Untuk mengulang file Excel dengan menggunakan enumerator File Foreach
Buat variabel string yang akan menerima jalur Excel saat ini dan nama file pada setiap iterasi perulangan. Untuk menghindari masalah validasi, tetapkan jalur Excel dan nama file yang valid sebagai nilai awal variabel. (Ekspresi sampel yang ditampilkan nanti dalam prosedur ini menggunakan nama variabel,
ExcelFile.)Secara opsional, buat variabel string lain yang akan menyimpan nilai untuk argumen Properti Yang Diperluas dari string koneksi Excel. Argumen ini berisi serangkaian nilai yang menentukan versi Excel dan menentukan apakah baris pertama berisi nama kolom, dan apakah mode impor digunakan. (Ekspresi sampel yang ditampilkan nanti dalam prosedur ini menggunakan nama
ExtPropertiesvariabel , dengan nilai awal "Excel 12.0;HDR=Yes".)Jika Anda tidak menggunakan variabel untuk argumen Properti yang Diperluas, maka Anda harus menambahkannya secara manual ke ekspresi yang berisi string koneksi.
Tambahkan kontainer Foreach Loop ke tab Alur Kontrol. Untuk informasi tentang cara mengonfigurasi Kontainer Perulangan Foreach, lihat Mengonfigurasi Kontainer Perulangan Foreach.
Pada halaman Koleksi Editor Perulangan Foreach, pilih enumerator File Foreach, tentukan folder tempat buku kerja Excel berada, dan tentukan filter file (biasanya *.xlsx).
Pada halaman Pemetaan Variabel, petakan Indeks 0 ke variabel string yang ditentukan pengguna yang akan menerima jalur Excel saat ini dan nama file pada setiap perulangan perulangan. (Ekspresi sampel yang ditampilkan nanti dalam prosedur ini menggunakan nama
ExcelFilevariabel .)Tutup Editor Perulangan Foreach.
Tambahkan pengelola koneksi Excel ke paket seperti yang dijelaskan di Tambahkan, Hapus, atau Bagikan Pengelola Sambungan dalam Paket. Pilih file buku kerja Excel yang sudah ada untuk koneksi guna menghindari kesalahan validasi.
Penting
Untuk menghindari kesalahan validasi saat Anda mengonfigurasi tugas dan komponen aliran data yang menggunakan pengelola koneksi Excel ini, pilih buku kerja Excel yang sudah ada di Editor Pengelola Sambungan Excel. Manajer koneksi tidak akan menggunakan buku kerja ini pada waktu proses setelah Anda mengonfigurasi ekspresi untuk properti ConnectionString seperti yang dijelaskan dalam langkah-langkah berikut. Setelah membuat dan mengonfigurasi paket, Anda dapat menghapus nilai properti ConnectionString di jendela Properti. Namun, jika Anda menghapus nilai ini, properti string koneksi manajer koneksi Excel tidak lagi valid sampai Perulangan Foreach berjalan. Oleh karena itu, Anda harus mengatur properti DelayValidation ke True pada tugas tempat pengelola koneksi digunakan, atau pada paket, untuk menghindari kesalahan validasi.
Anda juga harus menggunakan nilai default False untuk properti RetainSameConnection dari pengelola koneksi Excel. Jika Anda mengubah nilai ini menjadi True, setiap perulangan perulangan akan terus membuka buku kerja Excel pertama.
Pilih pengelola koneksi Excel baru, klik properti Ekspresi di jendela Properti, lalu klik elipsis.
Di Editor Ekspresi Properti, pilih properti ConnectionString, lalu klik elipsis.
Di Penyusun Ekspresi, masukkan ekspresi berikut ini:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""Perhatikan penggunaan karakter escape "\" untuk menghindari tanda kutip dalam yang diperlukan di sekitar nilai argumen Properti yang Diperluas.
Argumen Properti yang Diperluas tidak bersifat opsional. Jika Anda tidak menggunakan variabel untuk berisi nilainya, maka Anda harus menambahkannya secara manual ke ekspresi, seperti dalam contoh berikut:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties=Excel 12.0"Buat tugas dalam kontainer Foreach Loop yang menggunakan pengelola koneksi Excel untuk melakukan operasi yang sama pada setiap buku kerja Excel yang cocok dengan lokasi dan pola file yang ditentukan.
Untuk mengulangi tabel Excel dengan menggunakan enumerator Foreach ADO.NET Skema Rowset
Buat pengelola koneksi ADO.NET yang menggunakan Penyedia Microsoft ACE OLE DB untuk menyambungkan ke buku kerja Excel. Pada halaman Semua kotak dialog Pengelola Sambungan, pastikan Anda memasukkan versi Excel - dalam hal ini, Excel 12.0 - sebagai nilai properti Properti yang Diperluas. Untuk informasi selengkapnya, lihat Menambahkan, Menghapus, atau Berbagi Pengelola Sambungan dalam Paket.
Buat variabel string yang akan menerima nama tabel saat ini pada setiap iterasi perulangan.
Tambahkan kontainer Foreach Loop ke tab Alur Kontrol. Untuk informasi tentang cara mengonfigurasi kontainer Foreach Loop, lihat Mengonfigurasi Kontainer Perulangan Foreach.
Pada halaman Koleksi Editor Perulangan Foreach, pilih enumerator Foreach ADO.NET Schema Rowset.
Sebagai nilai Koneksi, pilih pengelola koneksi ADO.NET yang Anda buat sebelumnya.
Sebagai nilai Skema, pilih Tabel.
Catatan
Daftar tabel dalam buku kerja Excel menyertakan kedua lembar kerja (yang memiliki akhiran $) dan rentang bernama. Jika Anda harus memfilter daftar hanya untuk lembar kerja atau hanya rentang bernama, Anda mungkin harus menulis kode kustom dalam tugas Skrip untuk tujuan ini. Untuk informasi selengkapnya, lihat Bekerja dengan File Excel dengan Tugas Skrip.
Pada halaman Pemetaan Variabel, petakan Indeks 2 ke variabel string yang dibuat sebelumnya untuk menahan nama tabel saat ini.
Tutup Editor Perulangan Foreach.
Buat tugas dalam kontainer Foreach Loop yang menggunakan pengelola koneksi Excel untuk melakukan operasi yang sama pada setiap tabel Excel dalam buku kerja yang ditentukan. Jika Anda menggunakan Tugas Skrip untuk memeriksa nama tabel enumerasi atau untuk bekerja dengan setiap tabel, ingatlah untuk menambahkan variabel string ke properti ReadOnlyVariables dari tugas Skrip.
Lihat Juga
Memuat data dari atau ke Excel dengan SQL Server Integration Services (SSIS)
Mengonfigurasi Kontainer Perulangan Foreach
Menambahkan atau Mengubah Ekspresi Properti
Pengelola Koneksi Excel
Sumber Excel
Tujuan Excel
Bekerja dengan File Excel dengan Tugas Skrip