Mengulang File dan Tabel Excel dengan Kontainer Perulangan Foreach

Berlaku untuk: SQL Server (semua versi yang didukung) Integration Runtime SSIS 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

  1. 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.)

  2. 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.

  3. Tambahkan kontainer Foreach Loop ke tab Alur Kontrol . Untuk informasi tentang cara mengonfigurasi Kontainer Foreach Loop, lihat Mengonfigurasi Kontainer Perulangan Foreach.

  4. Pada halaman KoleksiEditor Perulangan Foreach, pilih enumerator File Foreach, tentukan folder tempat buku kerja Excel berada, dan tentukan filter file (biasanya *.xlsx).

  5. 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 .)

  6. Tutup Editor Perulangan Foreach.

  7. 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 Excel Pengelola Sambungan Editor. Pengelola koneksi tidak akan menggunakan buku kerja ini pada durasi 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 Foreach Loop 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.

  8. Pilih pengelola koneksi Excel baru, klik properti Ekspresi di jendela Properti, lalu klik elipsis.

  9. Di Editor Ekspresi Properti, pilih properti ConnectionString , lalu klik elipsis.

  10. Di Penyusun Ekspresi, masukkan ekspresi berikut:

    "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 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"  
    
  11. 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 mengulang tabel Excel dengan menggunakan enumerator Foreach ADO.NET Schema Rowset

  1. 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.

  2. Buat variabel string yang akan menerima nama tabel saat ini pada setiap iterasi perulangan.

  3. Tambahkan kontainer Foreach Loop ke tab Alur Kontrol . Untuk informasi tentang cara mengonfigurasi kontainer Foreach Loop, lihat Mengonfigurasi Kontainer Perulangan Foreach.

  4. Pada halaman KoleksiEditor Perulangan Foreach, pilih enumerator Foreach ADO.NET Schema Rowset.

  5. Sebagai nilai Koneksi, pilih pengelola koneksi ADO.NET yang Anda buat sebelumnya.

  6. 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.

  7. Pada halaman Pemetaan Variabel , petakan Indeks 2 ke variabel string yang dibuat sebelumnya untuk menyimpan nama tabel saat ini.

  8. Tutup Editor Perulangan Foreach.

  9. 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 yang dijumlahkan 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