Variabel Integration Services (SSIS)

  • Artikel

Berlaku untuk: Integration Runtime SSIS SQL Server di Azure Data Factory

Variabel menyimpan nilai yang dapat digunakan paket SQL Server Integration Services dan kontainer, tugas, dan penanganan aktivitasnya pada durasi. Skrip dalam tugas Skrip dan komponen Skrip juga dapat menggunakan variabel. Batasan prioritas yang mengurutkan tugas dan kontainer ke dalam alur kerja dapat menggunakan variabel saat definisi batasannya menyertakan ekspresi.

Anda dapat menggunakan variabel dalam paket Layanan Integrasi untuk tujuan berikut:

  • Memperbarui properti elemen paket pada durasi. Misalnya, Anda dapat secara dinamis mengatur jumlah executable bersamaan yang diizinkan oleh kontainer Foreach Loop.

  • Menyertakan tabel pencarian dalam memori. Misalnya, paket dapat menjalankan tugas Jalankan SQL yang memuat variabel dengan nilai data.

  • Memuat variabel dengan nilai data lalu menggunakannya untuk menentukan kondisi pencarian dalam klausa WHERE. Misalnya, skrip dalam tugas Skrip dapat memperbarui nilai variabel yang digunakan oleh pernyataan Transact-SQL dalam tugas Jalankan SQL.

  • Memuat variabel dengan bilangan bulat lalu menggunakan nilai untuk mengontrol perulangan dalam alur kontrol paket. Misalnya, Anda dapat menggunakan variabel dalam ekspresi evaluasi kontainer For Loop untuk mengontrol iterasi.

  • Mengisi nilai parameter untuk pernyataan Transact-SQL pada durasi. Misalnya, paket dapat menjalankan tugas Jalankan SQL lalu menggunakan variabel untuk mengatur parameter secara dinamis dalam pernyataan Transact-SQL.

  • Membangun ekspresi yang menyertakan nilai variabel. Misalnya, transformasi Kolom Turunan dapat mengisi kolom dengan hasil yang diperoleh dengan mengalikan nilai variabel dengan nilai kolom.

Variabel yang ditentukan sistem dan pengguna

Integration Services mendukung dua jenis variabel: variabel dan variabel sistem yang ditentukan pengguna. Variabel yang ditentukan pengguna ditentukan oleh pengembang paket, dan variabel sistem ditentukan oleh Integration Services. Anda dapat membuat variabel yang ditentukan pengguna sebanyak yang diperlukan paket, tetapi Anda tidak dapat membuat variabel sistem tambahan.

Semua variables-system dan user-defined-dapat digunakan dalam pengikatan parameter yang digunakan tugas Execute SQL untuk memetakan variabel ke parameter dalam pernyataan SQL. Untuk informasi selengkapnya, lihat Menjalankan Tugas dan Parameter SQLdan Mengembalikan Kode dalam Tugas Jalankan SQL.

Catatan

Nama variabel yang ditentukan pengguna dan sistem peka huruf besar/kecil.

Anda dapat membuat variabel yang ditentukan pengguna untuk semua jenis kontainer Integration Services: paket, kontainer Foreach Loop, kontainer For Loop, kontainer Urutan, tugas, dan penanganan aktivitas. Variabel yang ditentukan pengguna adalah anggota koleksi Variabel kontainer.

Jika Anda membuat paket menggunakan SSIS Designer, Anda dapat melihat anggota koleksi Variabel di folder Variabel pada tab Package Explorer dari SSIS Designer. Folder mencantumkan variabel dan variabel sistem yang ditentukan pengguna.

Anda dapat mengonfigurasi variabel yang ditentukan pengguna dengan cara berikut:

  • Berikan nama dan deskripsi untuk variabel .

  • Tentukan namespace untuk variabel.

  • Menunjukkan apakah variabel menaikkan peristiwa saat nilainya berubah.

  • Menunjukkan apakah variabel bersifat baca-saja atau baca/tulis.

  • Gunakan hasil evaluasi ekspresi untuk mengatur nilai variabel.

  • Buat variabel dalam cakupan paket atau objek paket seperti tugas.

  • Tentukan nilai dan jenis data variabel.

Satu-satunya opsi yang dapat dikonfigurasi pada variabel sistem adalah menentukan apakah mereka menaikkan peristiwa ketika mereka mengubah nilai.

Set variabel sistem yang berbeda tersedia untuk berbagai jenis kontainer. Untuk informasi selengkapnya tentang variabel sistem yang digunakan oleh paket dan elemennya, lihat Variabel Sistem.

Untuk informasi selengkapnya tentang skenario penggunaan kehidupan nyata untuk variabel, lihat Menggunakan Variabel dalam Paket.

Properti variabel

Anda dapat mengonfigurasi variabel yang ditentukan pengguna dengan mengatur properti berikut di jendela Variabel atau jendela Properti . Properti tertentu hanya tersedia di jendela Properti.

Catatan

Satu-satunya opsi yang dapat dikonfigurasi pada variabel sistem adalah menentukan apakah mereka menaikkan peristiwa ketika mereka mengubah nilai.

Deskripsi
Menentukan deskripsi variabel.

EvaluateAsExpression
Saat properti diatur ke True, ekspresi yang disediakan digunakan untuk mengatur nilai variabel.

Ekspresi
Menentukan ekspresi yang ditetapkan ke variabel.

Nama
Menentukan nama variabel.

Ruang nama
Layanan Integrasi menyediakan dua namespace layanan, Pengguna dan Sistem. Secara default, variabel kustom berada di namespace pengguna , dan variabel sistem berada di namespace layanan Sistem . Anda dapat membuat namespace layanan tambahan untuk variabel yang ditentukan pengguna dan mengubah nama namespace layanan Pengguna , tetapi Anda tidak dapat mengubah nama namespace layanan Sistem , menambahkan variabel ke namespace layanan Sistem , atau menetapkan variabel sistem ke namespace layanan yang berbeda.

RaiseChangedEvent
Saat properti diatur ke True, peristiwa OnVariableValueChanged dinaikkan saat variabel berubah nilai.

ReadOnly
Ketika properti diatur ke False, variabel adalah read\write.

Cakupan

Catatan

Anda hanya dapat mengubah pengaturan properti ini dengan mengklik Pindahkan Variabel di jendela Variabel .

Variabel dibuat dalam cakupan paket atau dalam cakupan kontainer, tugas, atau penanganan aktivitas dalam paket. Karena kontainer paket berada di bagian atas hierarki kontainer, variabel dengan fungsi cakupan paket seperti variabel global dan dapat digunakan oleh semua kontainer dalam paket. Demikian pula, variabel yang ditentukan dalam cakupan kontainer seperti kontainer For Loop dapat digunakan oleh semua tugas atau kontainer dalam kontainer For Loop.

Jika paket menjalankan paket lain dengan menggunakan tugas Jalankan Paket, variabel yang ditentukan dalam lingkup paket panggilan atau tugas Jalankan Paket dapat tersedia untuk paket yang dipanggil dengan menggunakan jenis konfigurasi Variabel Paket Induk. Untuk informasi selengkapnya, lihat Konfigurasi Paket.

SertakanInDebugDump
Menunjukkan apakah nilai variabel disertakan dalam file dump debug.

Untuk variabel dan variabel sistem yang ditentukan pengguna, nilai default untuk opsi InclueInDebugDump adalah true.

Namun, untuk variabel yang ditentukan pengguna, sistem mengatur ulang opsi IncludeInDebugDump ke false saat kondisi berikut terpenuhi:

  • Jika properti variabel EvaluateAsExpression diatur ke true, sistem mengatur ulang opsi IncludeInDebugDump ke false.

    Untuk menyertakan teks ekspresi sebagai nilai variabel dalam file cadangan debug, atur opsi IncludeInDebugDump ke true.

  • Jika jenis data variabel diubah menjadi string, sistem mengatur ulang opsi IncludeInDebugDump ke false.

Saat sistem mengatur ulang opsi IncludeInDebugDump ke false, ini mungkin menimpa nilai yang dipilih oleh pengguna.

Nilai
Nilai variabel yang ditentukan pengguna dapat berupa harfiah atau ekspresi. Nilai variabel tidak boleh null. Variabel memiliki nilai default berikut:

Jenis data Nilai default
Boolean FALSE
Jenis data numerik dan biner 0 (zero)
Jenis data karakter dan string (string kosong)
Object System.Object

Variabel memiliki opsi untuk mengatur nilai variabel dan jenis data nilai. Dua properti harus kompatibel: misalnya, penggunaan nilai string bersama dengan jenis data bilangan bulat tidak valid.

Jika variabel dikonfigurasi untuk dievaluasi sebagai ekspresi, Anda harus memberikan ekspresi. Pada durasi, ekspresi dievaluasi, dan variabel diatur ke hasil evaluasi. Misalnya, jika variabel menggunakan ekspresi DATEPART("month", GETDATE()) , nilai variabel adalah angka yang setara dengan bulan untuk tanggal saat ini. Ekspresi harus merupakan ekspresi valid yang menggunakan sintaks tata bahasa ekspresi SSIS. Saat ekspresi digunakan dengan variabel, ekspresi dapat menggunakan literal dan operator dan fungsi yang disediakan tata bahasa ekspresi, tetapi ekspresi tidak dapat mereferensikan kolom dari aliran data dalam paket. Panjang maksimum ekspresi adalah 4000 karakter. Untuk informasi selengkapnya, lihat Ekspresi Integration Services (SSIS).

ValueType

Catatan

Nilai properti muncul di kolom Jenis data di jendela Variabel .

Menentukan jenis data dari nilai variabel.

Skenario untuk menggunakan variabel

Variabel digunakan dengan berbagai cara dalam paket Layanan Integrasi. Anda mungkin akan menemukan bahwa pengembangan paket tidak maju jauh sebelum Anda harus menambahkan variabel yang ditentukan pengguna ke paket Anda untuk mengimplementasikan fleksibilitas dan pengelolaan yang diperlukan solusi Anda. Tergantung pada skenarionya, variabel sistem juga umum digunakan.

Ekspresi Properti Gunakan variabel untuk menyediakan nilai dalam ekspresi properti yang mengatur properti paket dan objek paket. Misalnya, ekspresi , SELECT * FROM @varTableName menyertakan variabel varTableName yang memperbarui pernyataan SQL yang dijalankan tugas Execute SQL. Ekspresi, DATEPART("d", GETDATE()) == 1? @[User::varPackageFirst]:@[User::varPackageOther]", memperbarui paket yang dijalankan tugas Jalankan Paket, dengan menjalankan paket yang ditentukan dalam varPackageFirst variabel pada hari pertama bulan dan menjalankan paket yang ditentukan dalam varPackageOther variabel pada hari lain. Untuk informasi selengkapnya, lihat Menggunakan Ekspresi Properti dalam Paket.

Ekspresi Aliran Data Gunakan variabel untuk menyediakan nilai dalam ekspresi yang digunakan oleh transformasi Kolom Turunan dan Pemisahan Bersyar untuk mengisi kolom, atau untuk mengarahkan baris data ke output transformasi yang berbeda. Misalnya, ekspresi, @varSalutation + LastName, menggabungkan nilai dalam VarSalutation variabel dan LastName kolom . Ekspresi, Income < @HighIncome, mengarahkan baris data di mana nilai Income kolom kurang dari nilai dalam HighIncome variabel ke output. Untuk informasi selengkapnya, lihat Transformasi Kolom Turunan, Transformasi Pemisahan Bersyarkat, dan Ekspresi SSIS.

Ekspresi Batasan Prioritas Berikan nilai yang akan digunakan dalam batasan prioritas untuk menentukan apakah eksekusi yang dapat dibatasi berjalan. Ekspresi dapat digunakan bersama dengan hasil eksekusi (keberhasilan, kegagalan, penyelesaian), atau alih-alih hasil eksekusi. Misalnya, jika ekspresi, @varMax > @varMin, mengevaluasi ke true, executable berjalan. Untuk informasi selengkapnya, lihat Menambahkan Ekspresi ke Batasan Prioritas.

Parameter dan Kode Pengembalian Berikan nilai untuk parameter input, atau simpan nilai parameter output dan kode pengembalian. Anda melakukan ini dengan memetakan variabel ke parameter dan mengembalikan nilai. Misalnya, jika Anda mengatur variabel varProductId ke 23 dan menjalankan pernyataan SQL, SELECT * from Production.Product WHERE ProductID = ?, kueri mengambil produk dengan ProductID 23. Untuk informasi selengkapnya, lihat Menjalankan Tugas dan Parameter SQLdan Mengembalikan Kode dalam Tugas Jalankan SQL.

Untuk Ekspresi Perulangan Berikan nilai untuk digunakan dalam ekspresi inisialisasi, evaluasi, dan penugasan dari For Loop. Misalnya, jika variabel varCount adalah 2 dan varMaxCount 10, ekspresi inisialisasi adalah @varCount, ekspresi evaluasi adalah @varCount < @varMaxCount, dan ekspresi penugasan adalah @varCount =@varCount +1, maka perulangan berulang 8 kali. Untuk informasi selengkapnya, lihat Untuk Kontainer Perulangan.

Konfigurasi Variabel Paket Induk Meneruskan nilai dari paket induk ke paket anak. Paket anak dapat mengakses variabel dalam paket induk dengan menggunakan konfigurasi variabel paket induk. Misalnya, jika paket anak harus menggunakan tanggal yang sama dengan paket induk, paket anak dapat menentukan konfigurasi variabel paket induk yang menentukan variabel yang ditetapkan oleh fungsi GETDATE dalam paket induk. Untuk informasi selengkapnya, lihat Menjalankan Tugas Paket dan Konfigurasi Paket.

Komponen Tugas dan Skrip Skrip Berikan daftar variabel baca-saja dan baca/tulis ke tugas Skrip atau komponen Skrip, perbarui variabel baca/tulis dalam skrip, lalu gunakan nilai yang diperbarui di dalam atau di luar skrip. Misalnya, dalam kode, numberOfCars = CType(Dts.Variables("NumberOfCars").Value, Integer), variabel numberOfCars skrip diperbarui oleh nilai dalam variabel , NumberOfCars. Untuk informasi selengkapnya, lihat Menggunakan Variabel di Tugas Skrip.

Tambahkan variabel

  1. Di SQL Server Data Tools (SSDT), buka paket Layanan Integrasi yang ingin Anda kerjakan.

  2. Di Penjelajah Solusi, klik dua kali paket untuk membukanya.

  3. Di SSIS Designer, untuk menentukan cakupan variabel, lakukan salah satu hal berikut:

    • Untuk mengatur cakupan ke paket, klik di mana saja pada permukaan desain tab Alur Kontrol .

    • Untuk mengatur cakupan ke penanganan aktivitas, pilih executable dan penanganan aktivitas pada permukaan desain tab Penanganan Aktivitas .

    • Untuk mengatur cakupan ke tugas atau kontainer, pada permukaan desain tab Alur Kontrol atau tab Penanganan Aktivitas , klik tugas atau kontainer.

  4. Pada menu SSIS , klik Variabel. Anda dapat secara opsional menampilkan jendela Variabel dengan memetakan perintah View.Variables ke kombinasi kunci yang Anda pilih di halaman Keyboard dari kotak dialog Opsi .

  5. Di jendela Variabel , klik ikon Tambahkan Variabel . Variabel baru ditambahkan ke daftar.

  6. Secara opsional, klik ikon Opsi Kisi , pilih kolom tambahan untuk ditampilkan dalam kotak dialog Opsi Kisi Variabel , lalu klik OK.

  7. Secara opsional, atur properti variabel. Untuk informasi selengkapnya, lihat Mengatur Properti Variabel User-Defined.

  8. Untuk menyimpan paket yang diperbarui, klik Simpan Item Terpilih pada menu File .

Kotak dialog Tambahkan Variabel

Gunakan kotak dialog Tambahkan Variabel untuk menentukan properti variabel baru.

Opsi

Kontainer
Pilih kontainer dalam daftar. Kontainer menentukan cakupan variabel. Kontainer dapat berupa paket atau executable dalam paket.

Nama
Ketik nama variabel.

Ruang nama
Tentukan namespace variabel. Secara default, variabel yang ditentukan pengguna berada di namespace pengguna .

Jenis nilai
Pilih jenis data.

Nilai
Ketik nilai. Nilai harus kompatibel dengan jenis data yang ditentukan dalam opsi Jenis nilai.

Baca-saja
Pilih untuk membuat variabel baca-saja.

Menghapus variabel

  1. Di SQL Server Data Tools (SSDT), buka proyek Layanan Integrasi yang berisi paket yang Anda inginkan.

  2. Di Penjelajah Solusi, klik kanan paket untuk membukanya.

  3. Pada menu SSIS , klik Variabel. Anda dapat secara opsional menampilkan jendela Variabel dengan memetakan perintah View.Variables ke kombinasi kunci yang Anda pilih di halaman Keyboard dari kotak dialog Opsi .

  4. Pilih variabel yang akan dihapus, lalu klik Hapus Variabel.

    Jika Anda tidak melihat variabel di jendela Variabel, klik Opsi Kisi lalu pilih Tampilkan variabel dari semua cakupan.

  5. Jika kotak dialog Konfirmasi Penghapusan Variabel terbuka, klik Ya untuk mengonfirmasi.

  6. Untuk menyimpan paket yang diperbarui, klik Simpan Item Terpilih pada menu File .

Mengubah cakupan variabel

  1. Di SQL Server Data Tools (SSDT), buka proyek Layanan Integrasi yang berisi paket yang Anda inginkan.

  2. Di Penjelajah Solusi, klik kanan paket untuk membukanya.

  3. Pada menu SSIS , klik Variabel. Anda dapat secara opsional menampilkan jendela Variabel dengan memetakan perintah View.Variables ke kombinasi kunci yang Anda pilih di halaman Keyboard dari kotak dialog Opsi .

  4. Pilih variabel lalu klik Pindahkan Variabel.

    Jika Anda tidak melihat variabel di jendela Variabel, klik Opsi Kisi lalu pilih Tampilkan variabel dari semua cakupan.

  5. Dalam kotak dialog Pilih Cakupan Baru , pilih paket atau kontainer, tugas, atau penanganan aktivitas dalam paket, untuk mengubah cakupan variabel.

  6. Untuk menyimpan paket yang diperbarui, klik Simpan Item Terpilih pada menu File .

Mengatur properti variabel yang ditentukan pengguna

Untuk mengatur properti variabel yang ditentukan pengguna di Integration Services, Anda bisa menggunakan salah satu fitur berikut:

  • Jendela variabel.

  • Jendela properti. Jendela Properti mencantumkan properti untuk mengonfigurasi variabel yang tidak tersedia di jendela Variabel : Deskripsi, EvaluateAsExpression, Expression, ReadOnly, ValueType, dan IncludeInDebugDump.

Catatan

Layanan Integrasi juga menyediakan sekumpulan variabel sistem yang propertinya tidak dapat diperbarui, dengan pengecualian properti RaiseChangedEvent.

Mengatur ekspresi pada variabel

Saat Anda menggunakan jendela Properti untuk mengatur ekspresi pada variabel yang ditentukan pengguna:

  • Nilai variabel dapat diatur oleh properti Nilai atau Ekspresi. Secara default, properti EvaluateAsExpression diatur ke False dan nilai variabel diatur oleh properti Nilai. Untuk menggunakan ekspresi untuk mengatur nilai, Anda harus terlebih dahulu mengatur EvaluateAsExpression ke True, lalu memberikan ekspresi di properti Ekspresi. Properti Nilai secara otomatis diatur ke hasil evaluasi ekspresi.

  • Properti ValueType berisi jenis data nilai dalam properti Nilai. Saat Nilai diatur oleh ekspresi, ValueType secara otomatis diperbarui ke jenis data yang kompatibel dengan hasil evaluasi ekspresi. Misalnya, jika Nilai berisi properti 0 dan ValueType berisi Int32 lalu Anda mengatur Ekspresi ke GETDATE(), Nilai berisi tanggal dan waktu saat ini dan ValueType diatur ke DateTime.

  • Jendela Properti untuk variabel menyediakan akses ke kotak dialog Penyusun Ekspresi . Anda dapat menggunakan alat ini untuk membuat, memvalidasi, dan mengevaluasi ekspresi. Untuk informasi selengkapnya, lihat Ekspresi Penyusun Ekspresi dan Layanan Integrasi (SSIS).

Saat Anda menggunakan jendela Variabel untuk mengatur ekspresi pada variabel yang ditentukan pengguna:

  • Untuk menggunakan ekspresi untuk mengatur nilai variabel, pertama-tama konfirmasikan bahwa jenis data variabel kompatibel dengan hasil evaluasi ekspresi lalu berikan ekspresi di kolom Ekspresi dari jendela Variabel . Properti EvaluateAsExpression di jendela Properti secara otomatis diatur ke True.

  • Saat Anda menetapkan ekspresi ke variabel, penanda ikon khusus ditampilkan di samping variabel. Penanda ikon khusus ini juga ditampilkan di samping pengelola koneksi dan tugas yang memiliki ekspresi yang diatur padanya.

  • Jendela Variabel untuk variabel menyediakan akses ke kotak dialog Penyusun Ekspresi . Anda dapat menggunakan alat ini untuk membuat, memvalidasi, dan mengevaluasi ekspresi. Untuk informasi selengkapnya, lihat Ekspresi Penyusun Ekspresi dan Layanan Integrasi (SSIS).

Di jendela Variabel dan Properti , jika Anda menetapkan ekspresi ke variabel, dan EvaluateAsExpression diatur ke True, Anda tidak dapat mengubah jenis data variabel.

Mengatur properti Namespace dan Nama

Nilai properti Name dan Namespace harus dimulai dengan huruf karakter alfabet seperti yang didefinisikan oleh Unicode Standard 2.0, atau garis bawah (_). Karakter berikutnya dapat berupa huruf atau angka seperti yang didefinisikan dalam Standar Unicode 2.0, atau garis bawah (_).

Mengatur Properti Variabel di Jendela Variabel

  1. Di SQL Server Data Tools (SSDT), buka proyek Layanan Integrasi yang berisi paket yang Anda inginkan.

  2. Di Penjelajah Solusi, klik kanan paket untuk membukanya.

  3. Pada menu SSIS , klik Variabel.

    Anda dapat secara opsional menampilkan jendela Variabel dengan memetakan perintah View.Variables ke kombinasi kunci yang Anda pilih di halaman Keyboard dari kotak dialog Opsi .

  4. Secara opsional, di jendela Variabel klik Opsi Kisi, lalu pilih kolom yang akan muncul di jendela Variabel dan pilih filter untuk diterapkan ke daftar variabel.

  5. Pilih variabel dalam daftar, lalu perbarui nilai di kolom Nama, Jenis Data, Nilai, Namespace, Naikkan Peristiwa Perubahan, Deskripsi, dan Ekspresi .

  6. Pilih variabel dalam daftar, lalu klik Pindahkan Variabel untuk mengubah cakupan.

  7. Untuk menyimpan paket yang diperbarui, pada menu File , klik Simpan Item Terpilih.

Atur Properti Variabel di Jendela Properti

  1. Di SQL Server Data Tools (SSDT), buka proyek Layanan Integrasi yang berisi paket yang Anda inginkan.

  2. Di Penjelajah Solusi, klik kanan paket untuk membukanya.

  3. Pada menu Tampilan, pilih Jendela Properti.

  4. Di SSIS Designer, klik tab Package Explorer dan perluas node Paket.

  5. Untuk memodifikasi variabel dengan cakupan paket, perluas simpul Variabel; jika tidak, perluas node Event Handler atau Executables hingga Anda menemukan simpul Variabel yang berisi variabel yang ingin Anda ubah.

  6. Klik variabel yang propertinya ingin Anda ubah.

  7. Di jendela Properti , perbarui properti variabel baca/tulis. Beberapa properti baca/baca saja untuk variabel yang ditentukan pengguna.

    Untuk informasi selengkapnya tentang properti, lihat Variabel Integration Services (SSIS).

  8. Untuk menyimpan paket yang diperbarui, pada menu File , klik Simpan Item Terpilih.

Memperbarui variabel secara dinamis dengan konfigurasi

Untuk memperbarui variabel secara dinamis, Anda dapat membuat konfigurasi untuk variabel, menyebarkan konfigurasi dengan paket, lalu memperbarui nilai variabel dalam file konfigurasi saat Anda menyebarkan paket. Pada durasi, paket menggunakan nilai variabel yang diperbarui. Untuk informasi selengkapnya, lihat Membuat Konfigurasi Paket.

Menggunakan Nilai Variabel dan Parameter dalam Paket Anak

Memetakan Parameter Kueri ke Variabel dalam Komponen Aliran Data