Pekerjaan mengambil data dari Excel, dan mengimpornya ke SQL Server tidak memiliki daya tarik atau pesona yang sama seperti, misalnya, melakukan heroik dengan kueri yang berkinerja buruk. Ini bisa jadi mengapa seseorang ragu-ragu sebelum mengajukan pertanyaan tentang bagaimana melakukannya. Rob Sheldon menenangkan keraguan dan ketakutan pribadi Anda dengan menjawab pertanyaan-pertanyaan yang memalukan itu Show
Pertanyaan-pertanyaan“Kapan saya harus menggunakan manajer koneksi Excel daripada komponen Sumber Excel?”Ini bukan masalah satu di atas yang lain. Jika paket SSIS Anda mengambil data dari buku kerja Excel, Anda memerlukan manajer koneksi dan komponen sumber. Manajer koneksi berfungsi sebagai antarmuka antara paket dan file Excel, atau lebih tepatnya, komponen sumber menggunakan manajer koneksi untuk mengakses data di file Excel Saat menyetel pengelola koneksi, Anda hanya perlu menyediakan jalur dan nama file untuk buku kerja Excel, pilih versi Excel, dan tentukan apakah baris pertama harus diperlakukan sebagai nama kolom, seperti yang ditunjukkan pada Gambar 1  Gambar 1. Mengonfigurasi manajer koneksi Excel untuk menunjuk ke buku kerja Excel Anda kemudian perlu mengonfigurasi komponen Sumber Excel untuk menggunakan manajer koneksi untuk mengakses data di file Excel. Anda tidak dapat mengonfigurasi pengaturan lain di komponen sumber tanpa terlebih dahulu menentukan manajer koneksi Excel yang berlaku. Gambar 2 memperlihatkan halaman Connection Manager dari Excel Source Editor. Seperti yang Anda lihat, opsi pertama yang tercantum adalah untuk manajer koneksi. Dari sana, Anda memilih mode akses data dan informasi relevan apa pun yang spesifik untuk mode yang dipilih Gambar 2. Halaman Connection Manager dari kotak dialog Excel Source Editor Komponen Sumber Excel mendukung empat jenis mode akses data
Semua referensi ke nama lembar kerja di komponen Sumber Excel menyertakan tanda dolar tambahan ($). Selain itu, setiap kali mereferensikan lembar kerja dalam pernyataan SQL, Anda harus membatasi nama lembar kerja, biasanya dengan menyertakan nama dalam tanda kurung. Pilihan lain yang berguna di halaman Pengelola Koneksi adalah Pratinjau, yang memungkinkan Anda melihat semua atau sebagian data yang dikembalikan dari spreadsheet Anda, berdasarkan mode akses data yang dipilih. Opsi ini memberikan cara praktis untuk memeriksa data untuk membantu mengonfirmasi bahwa Anda mengambil jenis informasi yang benar Halaman Kolom dari Editor Sumber Excel menentukan kolom mana yang harus diimpor dari lembar bentang Excel dan nama kolom keluaran, jika Anda ingin nama tersebut berbeda dari nama kolom yang dikembalikan dari lembar bentang. Gambar 3 menunjukkan halaman Kolom yang dikonfigurasi untuk menggunakan nama kolom yang sama seperti yang diambil dari spreadsheet Gambar 3. Halaman Kolom dari kotak dialog Editor Sumber Excel Nama kolom yang diambil dari spreadsheet dianggap sebagai kolom eksternal. Jika Anda telah memilih opsi Nama kolom baris pertama di manajer koneksi Excel Anda, nama akan datang langsung dari baris pertama dari data yang dikembalikan; Halaman terakhir di Editor Sumber Excel adalah Keluaran Kesalahan, yang memungkinkan Anda mengonfigurasi cara penanganan kesalahan atau pemotongan jika terjadi. Gambar 4 memperlihatkan pengaturan default untuk setiap kolom saat Anda pertama kali menyetel komponen Sumber Excel Gambar 4. Halaman Keluaran Kesalahan dari kotak dialog Editor Sumber Excel Secara default, setiap kesalahan atau pemotongan mengakibatkan kegagalan komponen. Namun, Anda dapat memilih untuk mengabaikan kesalahan atau pemotongan atau menampilkan baris yang berisi kesalahan atau pemotongan ke tujuan tertentu, seperti flat file, di mana Anda kemudian dapat memeriksa data yang dihasilkan Namun, seperti yang ditunjukkan sebelumnya, sebelum Anda dapat mengonfigurasi opsi apa pun di komponen Sumber Excel, Anda harus membuat manajer koneksi ke buku kerja Excel. Hanya dengan begitu Anda dapat mengonfigurasi mode akses, pengaturan kolom, dan penanganan kesalahan “Saya telah mencoba mengubah tipe data yang ditetapkan oleh komponen Sumber Excel ke data yang masuk tetapi tidak berhasil. Bagaimana Anda mengontrol tipe data di komponen sumber?”Pengandar yang digunakan untuk mengambil data Excel menyediakan sedikit kontrol, ketika harus menetapkan tipe data awal. Secara default, driver mengambil sampel delapan baris data pertama dan menentukan tipe data dari sana. Data yang masuk terbatas pada salah satu jenis SSIS berikut
Gambar 5 memperlihatkan Editor Tingkat Lanjut untuk komponen Sumber Excel. Komponen sedang mengambil data dari spreadsheet sederhana yang berisi lima kolom. Kolom pertama (EmpID) berisi semua bilangan bulat, sehingga properti DataType dikonfigurasi secara otomatis dengan nilai float presisi ganda [DT_R8]. Meskipun Anda dapat mengubah pengaturan properti, perubahan tersebut tidak akan bertahan. SSIS selalu menghasilkan tipe data untuk Anda saat memvalidasi data berdasarkan delapan baris pertama Gambar 5. Memverifikasi tipe data di editor lanjutan komponen sumber Jika kolom berisi tipe data campuran, SSIS sering menggunakan pendekatan "aturan mayoritas" saat menetapkan tipe. Misalnya, jika kolom berisi lima bilangan bulat dan tiga nilai teks, SSIS akan kembali menetapkan tipe data DT_R8 ke kolom tersebut. SSIS juga akan menetapkan tipe numerik ke seri, yang berarti pembagian empat/empat juga akan menghasilkan DT_R8. Sebagai konsekuensi dari pendekatan ini, semua nilai non-numerik dikembalikan sebagai nilai NULL Itu semakin buruk. Bayangkan Anda memiliki kolom yang merupakan campuran angka dan teks, dan Anda ingin tipe data DT_WSTR digunakan, tetapi jika delapan baris pertama berisi empat atau lebih nilai numerik, Anda akan mendapatkan DT_R8 suka atau tidak. Salah satu cara untuk mengatasi masalah ini adalah memperbarui kolom di lembar kerja agar dikonfigurasi sebagai Teks. Dengan begitu, meskipun kedelapan nilai adalah numerik, SSIS akan menetapkan tipe data DT_WSTR ke kolom tersebut. Tantangan dengan pendekatan ini adalah bahwa ini hanya berfungsi jika Anda memiliki akses ke file atau mengatakan bagaimana file dibuat. Jika Anda bekerja dengan sistem otomatis, pendekatan seperti itu bisa sangat sulit, kecuali Anda mengontrol bagaimana file dibuat Opsi lainnya adalah menambahkan opsi IMEX=1 ke string koneksi yang digunakan oleh manajer koneksi untuk mengakses file Excel. Opsi memberi tahu pengemudi untuk menggunakan mode Impor, yang mempertimbangkan pengaturan registri ImportMixedTypes=Teks. Ini memaksa data campuran untuk dikonversi menjadi teks. Perlu diketahui, bagaimanapun, bahwa menggunakan IMEX=1 dapat menyebabkan hasil yang tidak terduga jika Anda mencoba memperbarui data Anda juga dapat memanipulasi cara SSIS menetapkan tipe data dengan mengubah pengaturan registri TypeGuessRows, yang secara default diatur ke 8. Ini adalah pengaturan yang menentukan bahwa hanya delapan baris pertama yang dijadikan sampel. Anda dapat mempertimbangkan untuk menyetel TypeGuessRows ke 1 untuk membantu mempersempit fokus bagaimana jenis ditentukan. Namun, itu berarti Anda harus memastikan bahwa file Excel Anda berisi tipe data yang benar di baris pertama. Pendekatan ini juga menganggap Anda memiliki akses ke registri Bahkan jika Anda dapat mengakses registri, pastikan untuk mempertimbangkan semua operasi yang relevan sebelum mengubah pengaturan TypeGuessRows. Komponen dan paket SSIS lainnya, serta sistem lain, mungkin bergantung pada pengaturan saat ini. Misalnya, jika Anda mengubah pengaturan ke 1 dan nilai pertama kolom kurang dari 255 karakter, SSIS menetapkan tipe DT_WSTR(255) ke kolom yang diimpor. Kemudian, jika nilai berikutnya dalam kolom tersebut lebih dari 255 karakter, nilai tersebut dapat dipotong jika memasukkan data ke dalam tabel SQL Server. Sebenarnya, bergantung pada sifat data Anda, Anda mungkin sebenarnya ingin mengonfigurasi pengaturan TypeGuessRows dengan nilai yang lebih besar daripada yang lebih kecil Jelas, tidak ada solusi mudah untuk tipe data dan data Excel. Taruhan terbaik Anda adalah memastikan buku kerja Excel Anda diformat untuk memenuhi kebutuhan Anda. Semakin Anda dapat bekerja dalam batasan bagaimana tipe data ditetapkan secara default di SSIS, semakin mudah hidup Anda “Paket SSIS saya mengambil data dari lembar kerja Excel dan memasukkan data ke dalam tabel SQL Server. Alur data saya menampilkan pesan peringatan yang menunjukkan bahwa data dapat terpotong; . Apakah saya perlu melakukan sesuatu untuk menanggapi pesan peringatan tersebut?”Gambar 6. Konversi data untuk menghindari pemotongan Saat SSIS mengambil data dari buku kerja Excel, SSIS menetapkan salah satu dari dua tipe data karakter ke setiap kolom berbasis teks. DT_WSTR atau DT_NTEXT. Secara default, jika setidaknya salah satu dari delapan nilai pertama kolom lebih dari 255 karakter, SSIS menetapkan tipe data DT_NTEXT ke kolom; Dalam banyak kasus, SSIS menetapkan tipe data DT_WSTR ke kolom yang nilainya jauh lebih kecil dari 255. Tidak mengherankan, Anda mungkin memasukkan data tersebut ke kolom tujuan dengan panjang yang lebih kecil. Misalnya, Anda memasukkan data DT_WSTR(255) ke dalam kolom SQL Server yang dikonfigurasi dengan tipe data NVARCHAR(50). Jika Anda membuat aliran data sederhana yang berisi komponen Sumber Excel dan komponen Tujuan OLE DB, paket SSIS akan berjalan dengan baik, meskipun Anda akan menerima pesan peringatan pada komponen tujuan yang menunjukkan bahwa nilai dapat dipotong. Dengan kata lain, setiap nilai dengan panjang lebih dari 50 dapat dipotong saat memasukkan nilai tersebut ke dalam database Jika data terpotong, mungkin ada masalah dengan data sumber Anda yang perlu diselesaikan. Anda dapat menghindari pemotongan diam apa pun dengan menambahkan transformasi Konversi Data ke aliran data Anda, seperti yang diperlihatkan dalam Gambar 6. Dengan cara ini, paket setidaknya akan gagal, daripada data terpotong secara tidak sengaja Pada dasarnya, komponen transformasi mengubah setiap nilai DT_WSTR(255) menjadi nilai DT_WSTR(50). Gambar 7 menunjukkan seperti apa penyiapan konversi ini untuk tiga kolom di Editor Transformasi Konversi Data Gambar 7. Mengonfigurasi konversi data di Editor Transformasi Konversi Data Dalam hal ini, kami hanya menentukan panjang yang berbeda dan menetapkan alias output ke setiap kolom. Kami kemudian akan menggunakan alias ini saat mereferensikan kolom di komponen aliran data berikutnya. Ini menghilangkan peringatan pemotongan dan memastikan bahwa tidak ada nilai dengan panjang lebih dari 50 yang secara tidak sengaja terpotong. Sebaliknya, paket akan gagal sampai Anda memperbaiki datanya. Kemungkinannya adalah, Anda mungkin juga ingin memasukkan logika untuk menangani data yang menyinggung, bukan hanya gagal paket, tapi itu topik yang berbeda sama sekali. “Bagaimana cara membatasi kumpulan hasil saya ke kolom tertentu saat mengambil data dari buku kerja Excel?”Cara termudah untuk membatasi kolom yang Anda ambil dari lembar kerja Excel adalah dengan menentukan kolom mana yang akan disertakan atau tidak disertakan dalam komponen Sumber Excel Secara default, saat Anda pertama kali menyetel komponen, semua kolom di lembar kerja dipilih, tetapi jika Anda membuka halaman Kolom dari Editor Sumber Excel, Anda akan menemukan bahwa Anda dapat membatalkan pilihan kolom mana pun di kotak Kolom Eksternal yang Tersedia Angka 8. Memilih kolom mana yang akan disertakan dalam aliran data Anda Dalam hal ini, kolom EmpID telah dibatalkan pilihannya. Kolom tersebut kemudian secara otomatis dihapus dari kisi bawah pada halaman Kolom Jika Anda menghapus kolom dari komponen Sumber Excel setelah Anda menentukan komponen lain dalam aliran data, Anda harus memperbarui komponen tersebut seperlunya. Misalnya, jalur data Anda mungkin menyertakan transformasi Konversi Data yang secara khusus mereferensikan kolom yang dihapus. Komponen harus diperbarui. Hal yang sama berlaku untuk semua tujuan yang memetakan kolom yang dihapus ke kolom eksternal “Saya terkadang menerima peringatan di komponen Sumber Excel saya yang mengatakan bahwa kolom eksternal tidak sinkron dengan kolom sumber data. Tahu apa yang terjadi?”Terkadang saat Anda membuka paket SSIS, Anda mungkin menerima peringatan di komponen Sumber Excel yang mengatakan bahwa kolom eksternal tidak sinkron dengan kolom sumber data. Pesan yang membingungkan ini pada dasarnya mengatakan bahwa kolom yang masuk ke komponen tidak sinkron dengan yang keluar. Jika Anda menjalankan paket tanpa melakukan perubahan apa pun, kemungkinan besar Anda akan menerima pesan yang sama di hasil eksekusi Jika Anda malah membuka Editor Sumber Excel, Anda akan diminta untuk menyinkronkan kolom secara otomatis atau sinkronisasi akan terjadi tanpa diminta. Dalam kedua kasus tersebut, masalah akan tampak telah teratasi. Namun, jika Anda kemudian menjalankan paket, Anda mungkin menerima satu atau beberapa pesan peringatan tentang kolom keluaran yang tidak digunakan dalam aliran data Intinya semua ini adalah bahwa lembar kerja Excel sumber Anda kemungkinan besar telah berubah sejak Anda pertama kali mengatur komponen Sumber Excel Anda. Misalnya, Anda menyiapkan komponen berdasarkan lembar kerja sederhana yang berisi kolom EmpID, FirstName, MiddleName, LastName, dan BirthDate. Setelah membuat paket SSIS, seseorang (atau sesuatu) secara tidak sengaja menambahkan nilai xxx ke salah satu kolom yang tidak terpakai di spreadsheet, seperti yang ditunjukkan pada Gambar 9 Gambar 9. Lembar kerja Excel yang berisi data yang salah Secara default, saat Anda membuka paket SSIS, sumber data eksternal divalidasi terhadap komponen yang saat ini dikonfigurasi. Jika semuanya tidak sinkron sebagaimana mestinya, Anda akan menerima peringatan atau pesan kesalahan yang memberi tahu Anda bahwa ada masalah. Jika pesan yang Anda terima memperingatkan Anda bahwa kolom eksternal Anda tidak sinkron dengan kolom sumber data, Anda harus melihat lebih dekat pada komponen sumber melalui Editor Sumber Excel, di mana Anda mungkin menemukan bahwa kolom baru telah ditambahkan ke data Anda . Gambar 10 menunjukkan halaman Kolom Editor Sumber Excel, lengkap dengan kolom F6 dan F7 Gambar 10. Kolom tambahan dari lembar kerja Excel muncul di aliran data Perhatikan bahwa komponen sumber menambahkan kolom untuk mengakomodasi nilai xxx dan kolom yang cocok dengan kolom sebelum nilai. Paket Anda akan tetap berjalan, tetapi Anda akan menerima pesan peringatan tentang kolom keluaran yang tidak digunakan. Anda dapat memperbaikinya dengan memperbarui lembar kerja Excel itu sendiri atau dengan membatalkan pilihan kolom pada halaman Kolom Editor Sumber Excel. Apakah Anda mengambil salah satu dari langkah-langkah ini atau tidak melakukan apa-apa, situasi ini mungkin menunjukkan hal lain yang terjadi dengan file sumber Anda. Data yang salah mungkin merupakan hasil dari kesalahan manusia sederhana atau masalah dalam proses otomasi Anda, dalam hal ini, Anda mungkin memiliki masalah yang lebih besar untuk dihadapi daripada pesan peringatan sederhana “Salah satu kolom di lembar kerja Excel yang saya gunakan sebagai sumber data berisi nilai NULL untuk beberapa baris. Bagaimana cara mengirim baris tersebut ke file teks, daripada memuatnya ke dalam tabel database target?”Cara mudah untuk mengarahkan ulang baris yang berisi nilai NULL adalah dengan menambahkan transformasi Pemisahan Bersyarat ke aliran data Anda yang menentukan cara memproses baris ini. Sebagai contoh, misalkan kita mengambil data karyawan dari spreadsheet Excel dan mengirimkan data tersebut ke database SQL Server. Spreadsheet menyertakan kolom MiddleName yang dapat berisi nilai NULL. Untuk setiap baris yang nilai MiddleName adalah NULL, kami ingin mengarahkan ulang baris ke file teks sehingga aliran data kami terlihat seperti yang ditunjukkan pada Gambar 11 Gambar 11. Menambahkan transformasi Split Bersyarat ke aliran data Kunci untuk konfigurasi ini adalah menentukan kondisi dalam transformasi Pemisahan Bersyarat kami yang mengalihkan baris. Ini mudah dilakukan dengan menggunakan fungsi ISNULL untuk memeriksa nilai MiddleName untuk setiap baris. Jika nilainya NULL, baris dikirim ke keluaran IsNull. Gambar 12 menunjukkan Conditional Split Transformation Editor dengan ekspresi ISNULL yang ditentukan untuk keluaran IsNull Gambar 12. Menggunakan transformasi Split Bersyarat untuk mengarahkan ulang baris Perhatikan bahwa ekspresi kondisi hanyalah fungsi ISNULL dengan kolom yang diteruskan sebagai argumen. Setiap kali ekspresi bernilai True, yaitu, nilainya NULL, baris dikirim ke keluaran IsNull, yang terhubung ke tujuan Flat File. Itu berarti setiap baris dengan nilai MiddleName NULL disimpan ke flat file, bukannya dikirim ke komponen OLE DB Destination Jika kami juga ingin menguji kolom lain untuk nilai NULL, kami dapat menulis ekspresi yang lebih kompleks yang mencakup skenario lain. Namun dalam kasus ini, ekspresi ISNULL sederhana sudah cukup untuk menangani satu kolom “Saya membuat paket SSIS yang mengambil data dari buku kerja Excel. Namun, ketika saya mencoba men-debug paket saya menerima kesalahan dan saran agar saya menjalankan paket dalam mode 32-bit. Bagaimana aku melakukan itu?"Jika SQL Server versi 64-bit diinstal pada sistem Anda, SSIS akan berjalan secara otomatis dalam mode 64-bit. Namun, SSIS mungkin menggunakan penyedia 32-bit (baik Jet 4. 0 atau penyedia ACE OLEDB) untuk terhubung ke file Excel. Ketika ini terjadi, Anda akan menerima pesan kesalahan ketika Anda mencoba menjalankan paket yang mengatakan bahwa panggilan metode AcquireConnection ke manajer koneksi gagal. Anda mungkin juga akan menerima beberapa pesan kesalahan lainnya, salah satunya menyatakan bahwa penyedia tidak terdaftar dan paket harus dijalankan dalam mode 32-bit Untuk menjalankan paket dalam mode 32-bit, akses properti paket melalui Solution Explorer dan buka tab Debugging. Di sana Anda mengatur Run64BitRuntime ke False, seperti yang ditunjukkan pada Gambar 13 Gambar 13. Menyetel properti Run64BitRuntime ke False Setelah Anda mengonfigurasi ulang properti Run64BitRuntime, Anda seharusnya tidak memiliki masalah dalam mengeksekusi paket Anda, setidaknya tidak saat menjalankan paket di Business Intelligence Development Studio (BIDS) atau SQL Server Data Tools (SSDT). Jika Anda menjalankan paket di luar lingkungan pengembangan ini, Anda harus memastikan bahwa Anda melakukannya dalam mode 32-bit. Misalnya, jika Anda menjalankan paket dari baris perintah, gunakan utilitas dtexec versi 32-bit, atau jika Anda membuat pekerjaan Agen Server SQL untuk menjalankan paket, aktifkan opsi runtime 32-bit saat mengonfigurasi pekerjaan “Bagaimana Anda mengimpor rentang sel dari lembar kerja Excel, daripada semua data di lembar kerja?”Mengimpor rentang sel pada dasarnya adalah masalah mendefinisikan pernyataan SQL SELECT di dalam komponen Sumber Excel. Pernyataan SELECT harus menyertakan klausa FROM yang menentukan nama spreadsheet dan rentang sel. Misalnya, pernyataan SELECT berikut mengambil rentang A10 hingga E50 dari lembar kerja Emp1 1 PILIH * DARI [Emp1$A10:E50] Perhatikan bahwa nama lembar kerja menyertakan tanda dolar di belakangnya dan rentang dipisahkan oleh titik dua. Perhatikan juga bahwa tanda kurung digunakan untuk membatasi seluruh ekspresi. Namun, pernyataan SELECT tidak terbatas pada klausa SELECT dan FROM dan dapat menyertakan beberapa elemen yang Anda temukan dalam pernyataan SELECT lainnya. Misalnya, pernyataan SELECT berikut menambahkan klausa WHERE ke pernyataan dasar kita 1 2 PILIH * DARI [Emp1$A10:E50] DI MANA N ADALAH TIDAK NULL Klausa WHERE menentukan bahwa kolom N tidak boleh NULL agar baris dikembalikan. Dengan kata lain, semua baris yang berisi nilai aktual di kolom N akan disertakan dalam aliran data Nama kolom bisa sedikit rumit saat menggunakan pernyataan SELECT untuk mengambil rentang sel dari spreadsheet. Nama kolom akan bergantung pada tempat dimulainya rentang dan apakah opsi Baris pertama memiliki nama kolom dipilih di manajer koneksi Excel. Dalam beberapa kasus, nama kolom akan didasarkan pada baris data reguler. Misalnya, jika spreadsheet berisi informasi karyawan, seperti ID, nama, dan tanggal lahir, nama kolom Anda mungkin terlihat seperti 9, Gigi, N, Mathew, dan 1973-02-21, meskipun nama kolom asli . (Ini akan menjadi lebih jelas dalam sekejap. ) Untuk menggunakan pernyataan SELECT untuk mengambil data dari spreadsheet, buka Editor Sumber Excel, pilih perintah SQL dari daftar drop-down mode akses Data, dan masukkan pernyataan di kotak teks perintah SQL, seperti yang ditunjukkan pada Gambar 14 Gambar 14. Mengimpor rentang sel dari lembar kerja Excel Setelah Anda memasukkan pernyataan Anda, Anda dapat mengklik tombol Pratinjau untuk menampilkan data (atau bagian dari data). Gambar 15 memperlihatkan jendela Preview Query Results. Seperti yang Anda lihat, nama kolom terdiri dari deretan data, dimulai dengan nilai 9. N dalam hal ini ternyata adalah inisial tengah seseorang. Data aktual kemudian mengikuti Gambar 15. Pratinjau data yang diambil dari spreadsheet Excel Karena nama kolom seringkali hanya berupa deretan data, Anda mungkin ingin menetapkan nama yang lebih berguna ke kolom keluaran, yang dapat Anda lakukan di halaman Kolom Editor Sumber Excel, seperti yang ditunjukkan pada Gambar 16 Gambar 16. Menetapkan nama kolom yang ramah pengguna ke kolom keluaran Perhatikan bahwa nama kolom eksternal hanyalah baris pertama dari data, tetapi kami telah mengubah nama kolom keluaran agar lebih mudah memahami jenis data yang sedang kami kerjakan dan untuk membedakan satu kolom dari yang lain. Kami akan menggunakan nama kolom baru ini di komponen selanjutnya dalam aliran data “Paket SSIS saya menyertakan transformasi Konversi Data untuk mengurangi ukuran kolom string Unicode yang diambil dari spreadsheet Excel. Apakah ada cara untuk mengalihkan baris yang berisi nilai terlalu besar untuk ukuran kolom yang baru?”Cara termudah untuk mengalihkan baris dalam hal ini adalah dengan menambahkan transformasi Pemisahan Bersyarat ke aliran data Anda. Misalnya, aliran data Anda saat ini menyertakan komponen Sumber Excel untuk mengambil data dari spreadsheet, transformasi Konversi Data untuk mengonversi kolom DT_WSTR(255) menjadi kolom DT_WSTR(50), dan komponen Tujuan OLE DB yang tersambung ke SQL . Anda dapat menambahkan transformasi Pemisahan Bersyarat setelah komponen sumber untuk menangani nilai apa pun dengan panjang lebih dari 50 sebelum Anda mencoba melewatkannya melalui transformasi Konversi Data, seperti yang ditunjukkan pada Gambar 17 Gambar 17. Menambahkan transformasi Split Bersyarat ke aliran data Dalam hal ini, transformasi Split Bersyarat mengirimkan baris dengan nilai yang terlalu besar ke file datar; . Triknya adalah memastikan ekspresi Anda benar saat menentukan kondisi dalam transformasi. Ekspresi inilah yang menentukan baris mana yang akan dialihkan ke flat file. Gambar 18 menunjukkan kondisi tunggal, tetapi ekspresi yang terkait dengan kondisi tersebut menangani ketiga kolom string. Nama Depan, Nama Tengah, dan Nama Belakang Gambar 18. Menentukan kondisi yang menentukan baris mana yang dialihkan Ekspresi sebenarnya terdiri dari tiga bagian, satu untuk setiap kolom, dihubungkan dengan atau (. ) operator kondisional. Ekspresi disertakan di sini sehingga Anda dapat melihatnya secara keseluruhan dengan lebih baik 1 (. ISNULL(Nama Depan) && . LEN(FirstName) > 50) || (. ISNULL(Nama Tengah) && . LEN(MiddleName) > 50) || (. ISNULL(Nama Belakang) && LEN(LastName) > 50) Pada dasarnya, ekspresi menguji setiap kolom untuk memverifikasi bahwa itu bukan NULL dan panjangnya lebih dari 50. Jika kedua kondisi ini True untuk salah satu kolom, maka baris dialihkan ke output RowOut, yang terhubung ke komponen Flat File Destination. Jika tidak ada kolom string yang memenuhi syarat ini, baris akan mengikuti jalur data biasa dan dikirim ke transformasi Konversi Data. Dengan mengarahkan ulang baris dengan cara ini, Anda dapat memeriksa salah satu dari nilai besar yang tidak terduga tersebut untuk menentukan apa yang mungkin terjadi dengan data sumber Anda “Bagaimana cara menggabungkan data dari beberapa lembar kerja dalam satu file Excel?”Gambar 19. Mengambil data dari beberapa lembar kerja SSIS memudahkan untuk menggabungkan data dari beberapa lembar kerja selama Anda tahu berapa banyak lembar kerja dan apa namanya dan yakin struktur data serupa di antara mereka. Misalnya, kita bekerja dengan buku kerja Excel yang berisi data karyawan. Buku kerja mencakup dua lembar kerja (Emp1 dan Emp2), lembar kerja berisi kolom yang sama (EmpID, Nama Depan, Nama Tengah, Nama Belakang, dan Tanggal Lahir), dan setiap lembar kerja berisi subset datanya sendiri. Anda bisa membuat aliran data yang menyertakan dua komponen Excel Source dan komponen Union All untuk menggabungkan data bersama, seperti yang ditunjukkan pada Gambar 19 Dua komponen Sumber Excel menggunakan pengelola koneksi yang sama untuk mengakses file buku kerja. Namun, setiap komponen sumber menunjuk ke lembar kerja yang berbeda di dalam file tersebut. Gambar 20 menunjukkan halaman Connection Manager di Excel Source Editor untuk worksheet kedua (emp2). Perhatikan bahwa Anda memilih nama lembar kerja dari daftar drop-down Name of the Excel sheet, kecuali jika Anda menyertakan nama sebagai bagian dari pernyataan SQL Gambar 20. Menentukan lembar kerja Excel di Editor Sumber Excel Dalam beberapa kasus, lembar kerja tidak akan disusun secara identik, tetapi akan disusun cukup mirip untuk bekerja dengannya bersama-sama. Jika ini terjadi, Anda mungkin harus menggunakan pernyataan SQL untuk mengambil rentang sel saja, memilih kolom tertentu dari halaman Kolom, atau kombinasi keduanya. Tujuannya adalah untuk mendapatkan jalur data serupa yang berasal dari kedua komponen sumber. Dalam hal ini, tidak masalah apakah lembar kerja tersebut berasal dari buku kerja yang sama atau dari buku kerja yang berbeda. Yang penting adalah jalur data itu. Anda kemudian dapat menggunakan transformasi Union All untuk menggabungkannya bersama. Gambar 21 memperlihatkan Union All Transformation Editor, dengan dua set kolom input dan satu set kolom output Gambar 21. Mengonfigurasi transformasi Union All untuk menggabungkan dua sumber data Setelah Anda menggabungkan dua jalur data menjadi satu jalur, Anda dapat mengirim data ke tujuan Anda atau melakukan transformasi lain yang diperlukan. Namun, salah satu cara pendekatan ini menjadi sedikit rumit adalah jika Anda tidak mengetahui nama semua spreadsheet Anda. Dalam kasus seperti itu, Anda mungkin perlu menambahkan komponen Skrip yang mengambil nama spreadsheet, lalu menggunakan wadah Foreach Loop untuk melakukan iterasi melalui daftar nama. Selain itu, jika spreadsheet Anda disiapkan terlalu berbeda satu sama lain untuk menggabungkan aliran data bersama dengan mudah, Anda mungkin harus mengubah data terlebih dahulu sebelum menggabungkan jalur data atau menangani operasi sebagai aliran data terpisah “Saya menggunakan SSIS untuk mengambil data dari lembar kerja Excel dan memasukkan data ke dalam tabel SQL Server. Tabel mencakup beberapa kolom VARCHAR. Saya menerima pesan kesalahan yang menyatakan bahwa saya tidak dapat mengubah data Unicode menjadi data non-Unicode. Langkah apa yang harus saya ambil untuk mengubah data?”Gambar 22. Menambahkan Konversi Data Anda dapat menambahkan transformasi Konversi Data ke aliran data untuk mengonversi kolom string dari tipe data DT_WSTR ke tipe data DT_STR. Misalnya, Anda mungkin memiliki aliran data sederhana yang hanya terdiri dari komponen Sumber Excel dan komponen Tujuan OLE DB. Anda dapat menambahkan transformasi Konversi Data setelah komponen sumber untuk mengonversi data dari Unicode ke non-Unicode, seperti yang ditunjukkan pada Gambar 22 Saat mengonfigurasi transformasi Konversi Data, Anda cukup menentukan tipe data target dan memberikan nama alias untuk kolom keluaran. Anda juga dapat memanfaatkan konversi ini untuk memperkecil ukuran kolom. Gambar 23 menunjukkan Editor Transformasi Konversi Data yang dikonfigurasi untuk mengonversi tiga kolom string dari DT_WSTR(255) menjadi DT_STR(50) Gambar 23. Mengonversi data Unicode menjadi data non-Unicode Perhatikan bahwa sebuah alias output ditugaskan ke setiap kolom. Kami kemudian akan menggunakan alias keluaran di komponen selanjutnya. Misalnya, dalam kasus ini, kami akan memperbarui komponen OLE DB Destination kami untuk memetakan kolom tujuan ke nama alias, seperti yang ditunjukkan pada Gambar 24 Gambar 24. Memetakan data yang dikonversi ke data target di OLE DB Destination Editor SSIS sekarang seharusnya dapat memuat data ke tabel target tanpa masalah, kecuali seseorang memasukkan nilai ke dalam spreadsheet yang tidak dapat dikonversi ke nilai non-Unicode atau nilai yang panjangnya lebih dari 50 karakter. Itu sebabnya, jika Anda berencana untuk mengonversi data dengan cara ini, Anda mungkin ingin menambahkan penanganan kesalahan ke paket Anda, yang akan menjadi topik pertanyaan berikutnya. “Dalam aliran data saya, saya mengonversi data yang saya ambil dari spreadsheet Excel ke tipe data VARCHAR. Bagaimana cara menangani kesalahan untuk data yang tidak dapat dikonversi?”Tidak jarang saat mengambil data dari spreadsheet Excel harus mengonversi kolom Unicode menjadi kolom non-Unicode. Misalnya, Anda mungkin mencoba mengakomodasi basis data lama yang skemanya tidak dapat diperbarui. Di SSIS, itu berarti mengonversi satu atau lebih kolom dari tipe data DT_WSTR ke tipe data DT_STR. Selama data string Anda terdiri dari karakter Latin dasar, Anda seharusnya tidak mengalami masalah. Tetapi jika Anda menemukan diri Anda dihadapkan dengan nilai sesekali yang ditulis dalam bahasa seperti Cina, Jepang, atau Arab, Anda harus menangani baris yang berisi nilai-nilai ini; Jalur yang paling tidak resisten dalam hal ini adalah menambahkan penanganan kesalahan ke komponen Konversi Data Anda, dalam hal ini, Anda akan menampilkan potensi kesalahan dan pemotongan ke file teks atau jenis tujuan lainnya, seperti yang ditunjukkan pada Gambar 25 Gambar 25. Mengeluarkan baris yang menghasilkan kesalahan ke file teks Perhatikan bahwa jalur data yang menghubungkan transformasi Konversi Data ke komponen Flat File Destination berwarna merah dan menyertakan catatan yang menyatakan bahwa ini adalah keluaran kesalahan. Untuk mengonfigurasi keluaran kesalahan transformasi, buka Editor Transformasi Konversi Data dan klik tombol Konfigurasi Keluaran Kesalahan. Ini akan membuka kotak dialog Configure Error Output, yang ditunjukkan pada Gambar 26 Gambar 26. Konfigurasi penanganan kesalahan dalam transformasi Konversi Data Untuk setiap kolom yang dikonversi, pilih opsi Alihkan Baris untuk kesalahan dan pemotongan untuk memastikan Anda menangkap semua kemungkinan skenario. Kemudian tutup kotak dialog dan pastikan jalur keluaran kesalahan terhubung ke komponen Flat File Destination. Dengan begitu, setiap kali terjadi kesalahan atau pemotongan, seperti elemen bahasa yang memerlukan kolom Unicode, Anda akan dapat memeriksa kesalahan dalam flat file “Bagaimana cara mengambil data dari beberapa file buku kerja Excel?”Gambar 27. Menyiapkan wadah Foreach Loop SSIS menyertakan wadah Foreach Loop untuk mendukung operasi seperti iterasi melalui banyak file. Anda dapat menggunakan penampung untuk menjalankan tugas Aliran Data untuk setiap file Excel. Untuk melakukannya, mulailah dengan menambahkan wadah Foreach Loop ke aliran kontrol Anda, lalu tambahkan tugas Aliran Data ke wadah, seperti yang ditunjukkan pada Gambar 27 Untuk mendukung operasi ini, Anda juga perlu menambahkan variabel yang ditentukan pengguna ke paket Anda. Variabel akan menyimpan jalur saat ini dan nama file dari setiap file Excel setiap kali paket melewati wadah. Untuk contoh kami di sini, kami telah membuat variabel string yang disebut nama file dan menetapkan nilai awalnya ke nama yang sepenuhnya memenuhi syarat dari file Excel pertama di direktori target kami Setelah kami membuat variabel kami, kami dapat mengedit wadah Foreach Loop. Klik dua kali wadah dan kemudian pergi ke halaman Koleksi, tempat kami mengatur folder target dan jenis file, seperti yang ditunjukkan pada Gambar 28 Gambar 28. Mengonfigurasi halaman Collection dari Foreach Loop Editor Dalam hal ini, kami menggunakan C. \ExcelData\ sebagai folder target dan *. xlsx sebagai file sebagai data sumber kami. Hasilnya, wadah Foreach Loop kami akan berjalan sekali untuk masing-masing wadah. xlsx di folder target. Kami telah meninggalkan semua opsi lain dengan pengaturan defaultnya Selanjutnya kita lompat ke halaman Pemetaan Variabel. Di sini kita memilih variabel nama file di grid, dan memastikan bahwa 0 ditentukan untuk nilai indeks. Gambar 29 menunjukkan bagaimana kami menambahkan variabel ke halaman Pemetaan Variabel. Sekarang variabel itu akan menyimpan nama yang memenuhi syarat untuk setiap file saat kita mengulang koleksi Gambar 29. Mengonfigurasi halaman Pemetaan Variabel dari Foreach Loop Editor Ada satu langkah lain yang harus kita ambil untuk menyiapkan paket SSIS kita untuk mengulang sekumpulan file. Kami harus memperbarui salah satu properti di manajer koneksi Excel kami. Dalam hal ini, kami ingin menetapkan ekspresi ke properti ConnectionString. Ekspresi properti menggunakan variabel nama file untuk menyisipkan jalur dan nama file yang benar ke dalam string koneksi. Gambar 30 memperlihatkan kotak dialog Pembuat Ekspresi untuk properti ConnectionString. Dalam hal ini, kami menggunakan penyedia ACE OLEDB untuk terhubung ke file Excel Gambar 30. Membuat ekspresi untuk properti ConnectionString Karena kami telah membuat ekspresi properti, pengelola koneksi sekarang akan menunjuk ke file terbaru setiap kali wadah Foreach Loop beralih melalui file dan selanjutnya menjalankan tugas Aliran Data. Tugas Aliran Data akan berisi komponen Sumber Excel dan komponen lain yang diperlukan. Komponen sumber itu sendiri dikonfigurasi seperti biasa. Kontainer Foreach Loop yang dikombinasikan dengan ekspresi properti di manajer koneksi Excel inilah yang membuat operasi ini dinamis. Komponen Sumber Excel adalah bisnis seperti biasa Semua ini, tentu saja, merupakan ikhtisar dasar tentang cara menggunakan wadah Foreach Loop dan ekspresi properti. Pastikan untuk merujuk ke dokumentasi Microsoft untuk lebih spesifik tentang cara kerja semua komponen ini. Perlu diingat, bagaimanapun, konfigurasi yang kita bahas di sini mengasumsikan bahwa Anda mengetahui nama masing-masing spreadsheet dan dikonfigurasi secara identik. Jika bukan itu masalahnya, Anda harus memasukkan lebih banyak logika ke dalam paket Anda untuk mencakup skenario apa pun yang berlaku
20 20
2 Bagaimana cara menggunakan file Excel sebagai file sumber saat kolom terus berubah?Kemudian Anda perlu pergi ke opsi “Edit Kueri” dan pilih “Pengaturan sumber data” seperti yang ditunjukkan di bawah ini. Setelah memilih opsi Anda akan mendapatkan jendela seperti berikut. Maka Anda perlu mengklik "Ubah Sumber. ” dan pilih file excel lainnya seperti yang ditunjukkan di bawah ini. Setelah memilih file tutup pengaturan sumber data.
Bagaimana cara membuat file Excel secara dinamis dalam paket SSIS?Buat File Excel Secara Dinamis Di SSIS . Langkah 1. Buat Variabel untuk menjadikan Paket SSIS Anda Dinamis. . Langkah 2. Buat ADO. Koneksi .NET dalam Paket SSIS untuk digunakan dalam Tugas Skrip. . Langkah3. Tambahkan Variabel ke Tugas Skrip untuk digunakan dari Paket SSIS Bagaimana cara menggunakan sumber Excel di SSIS?Pada menu SSIS, pilih Sambungan baru. Di kotak dialog Tambah Pengelola Sambungan SSIS, pilih EXCEL lalu Tambah. Buat manajer sambungan pada saat yang sama saat Anda mengonfigurasi Sumber Excel atau Tujuan Excel pada halaman Manajer sambungan Editor Sumber Excel atau Editor Tujuan Excel
Masalah apa yang Anda hadapi saat menggunakan sumber Excel di SSIS?Masalah SSIS Excel . Ini adalah permasalahan yang biasa. mencoba memuat data dari file Excel setengah dari data datang sebagai nol, atau kolom dengan lebih dari 255 karakter terpotong Untuk Excel versi 64 bit Untuk Excel versi 32 bit |
Bagaimana cara menggunakan excel sebagai file sumber ketika kolom terus berubah ssis?
Pos Terkait
Copyright © 2024 ketajaman Inc.
