download sample code
Apakah Anda pernah mengalami sebuah kasus dimana SSIS package yang telah Anda buat berhasil di-execute dengan menggunakan BIDS, tetapi fail pada saat di-execute dengan menggunakan Job? Apabila ya, mungkin Anda melewatkan satu hal kecil yang seperti kebanyakan orang sering lupa untuk dikerjakan yaitu mengatur ProtectionLevel SSIS Package.
Secara default, ProtectionLevel SSIS Package di-set dengan EncryptSensitiveWithUserKey. Dengan setting tersebut, informasi sensitive yang ada di sebuah package, seperti password database yang terdapat pada connection, akan di-encrypt berdasarkan user yang terakhir memodifikasi package. Apabila user lain akan membuka package atau meng-execute-nya, informasi sensitive tadi akan diganti dengan nilai kosong dan user tersebut harus mengisinya. Pada kasus execute package dengan menggunakan Job, apabila user account yang menjalankan service SQL Server Agent berbeda dengan user account yang terakhir memodifikasi package, informasi tadi tetap bernilai kosong dan mengakibatkan job fail.
Cara termudah untuk men-solve problem nya adalah mengubah user account service SQL Server Agent dengan user account yang terakhir memodifikasi package. Akan tetapi cara ini tentunya kurang engineering –kalau mengambil kata-kata teman saya- dan tidak disarankan. Karena bisa saja ada banyak orang yang memodifikasi package dan itu berarti kita harus mengubah account SQL Server Agent setiap kali akan meng-execute Job. Bayangkan apabila ada lima buah package yang dijadikan Job, dan kelima package-nya dibuat oleh orang yang berbeda. Hanya satu dari lima job tadi yang berhasil sedangkan sisanya fail di-execute.
SSIS telah menyediakan sebuah fitur bernama SSIS Package Configuration untuk menyimpan informasi/properti yang terdapat di sebuah package. Kita dapat menyimpan properti nya di database SQL Server, XML File, Registry, atau Environment Variable.
Dalam contoh yang dapat Anda download, saya memiliki sebuah SSIS Package untuk mengambil data Product dari database AdventureWorks. Untuk melakukan koneksi ke database tersebut, saya menggunakan SQL Server Authentication sebagai Authentication Mode-nya.
Setelah connection, Data Flow Task beserta ETL nya selesai dibuat, hal yang saya lakukan adalah menge-set properti Protection Level Package dengan DontSaveSensitive
Kemudian saya membuka menu Package Configurations dari menu SSIS > Package Configuration. Setelah terbuka, centang Enable package configurations, lalu klik tombol Add
Klik Next pada Welcome to the Package Configuration Wizard
Pada Select Configuration Type, pilih XML configuration file sebagai configuration type, lalu klik browse untuk membuat sebuah config file dengan nama AdventureWorksConnection.dtsConfig, kemudian klik tombol Next
Pada Select Properties to Export, beri tanda centang pada properti ConnectionString untuk koneksi AdventureWorks2008 lalu klik Next.
Beri nama configuration-nya dengan AdventureWorksConnection lalu klik Finish. Klik Close untuk menutup Package Configuration
Dengan konfigurasi ini connection string AdventureWorks disimpan di dalam file AdventureWorksConnection.dtsConfig, dan setiap kali package-nya dibuka atau di-execute, properti ConnectionString AdventureWorks akan diisi dengan nilai yang terdapat pada config file.
Sebelum Anda membuka atau meng-execute package, ada baiknya membuka file AdventureWorksConnection.dtsConfig karena pada ConfiguredValue untuk Configuration AdventureWorks2008 belum terdapat Password. Anda dapat menambahkan password-nya setelah User ID=sa;Password=***, *** adalah password untuk User sa.
