Gerçekleştirdiğim projelerden sıkça karşıma çıkan ve hepimizin yoğun bir şekilde boğuşacağı "insert-update" işleminin SSIS ile nasıl çözüleceğini anlatmaya çalışacağım.
Normal şartlar altında, aklımıza gelebilecek ilk senaryo veri kaynağından verilerin bulk olarak(doğrudan) SQL Server'daki bir geçici tabloya alınması ve bu geçici tablo aracılığı ile var olan kayıtlarımızın güncellenmesi olur.
SSIS'in en büyük artılarından birisi ve belkide en önemlisi; verileri veritabanına almadan önce, temizlik - eleme gibi işlemlerin yapılabilmesidir. Çalıştığımız veritabanları genel olarak zaten çok büyük yapıdadırlar. Dolayısıyla geçici tablolar performans açısından kayıp anlamına gelirler. SSIS, veritabanına veriler alınmadan önce işlem yapacağından dolayı SQL Server'ı yormamızı engeller.
Senaryomuz gereği, SQL Server'da Kisi isimli bir tablo oluşturuyoruz... Bu tabloda Id, Ad, Soyad ve Pozisyon olmak üzere 4 kolon mevcut ve her ay sonunda çalıştırdığımız bir job ile mevcut olan kayıtlarımızın pozisyon bilgilerinin güncellenmesi(Görev değişikliği ya da terfi işlemleri gibi) ya da şirketimize yeni alınan bir kişinin tablomuza eklenmesi işlemleri gerçekleştiriliyor. İşlem esnasında hatalı kayıt ayıklamaları da yapılmakta.
Kısaca senaryomuzu açıkladıktan sonra nasıl yol alacağımızı izleyelim.
1. Kisi Tablosu
Tablomuzu aşağıdaki gibi oluşturup bazı kayıtlar giriyoruz.
2. Güncel Kayıtların Olduğu Text Dosyası
Bu aşamada SQL Server Import/Export Wizard yardımıyla, Kisi tablomuzdaki kayıtları öncelikle yeni bir text dosyasına aktarıyoruz. Daha sonra üzerinde hatalı oluşmasını sağlayacak bir satır, yeni ve hatasız bir satır ve update işleminin gerçekleştiğini görebilmek için yine var olan bir kayıdın güncel halini yeni dosyamızda düzenliyoruz.
3. Data Flow Task'in Hazırlanması
Temel hazırlıklarımızın ardından, asıl işin yapılacağı olan Integration Services tarafına geçiyoruz. Öncelikle yeni oluşturacağımız projeye, bir adet Data Flow Task sürükleyip bırakıyoruz. Daha sonra Data Flow Task içeriğini aşağıdaki gibi adım adım güncelliyoruz.
Flat File Source ile yeni yukarıda hazırlamış olduğumuz güncel kayıtlarımızı alma işlemini gerçekleştiriyoruz. Bir sonraki adımda hatalı ayıklama işlemi için gerekli olan Conditional Split Component kullanıyoruz. Bu adımda kullandığımız source text olduğu için kolonlardaki değerlerin boş olup olmadığını kontrol ediyoruz. (Data Source olarak başka bir kaynağınız varsa bu kontrolün dışında ISNULL() fonksiyonunu kullanmakta gerekebilir)
Conditional Split'de oluşturduğumuz iki output'dan "Hatalı Satırlar"ı Flat File Destination'a bağlıyoruz ve bir text dosyaya daha sonra analiz edilebilmesi için yazdırıyoruz. (Bu adımda Data Flow sonrası Send Mail Task hatalı kayıtların bulunduğu text dosyasını mail atabiliriz)
Lookup Component tarafında SQL Serverdaki data source ile Text dosyamızın metadataları aynı olmak zorunda olduğundan bir sonraki adımda küçük bir data conversion işlemi gerçekleştiriyoruz. (Bu işlemi Flat File Connection Manager tarafındaki Advanced tabında da gerçekleştirebiliriz)
Gerekli conversion işleminin altından Lookup işlemine geliyoruz. Connection Manager olarak OLEDB seçiyoruz ve gerekli SQL Server'a bağlanabilmek için gerekli düzenlemeleri Connection Manager kısmında yapıyoruz. Daha sonra Columns sekmesini aşağıdaki gibi ayarlıyoruz. Biz sadece Id üzerinden gerekli lookup işlemini gerçekleştireceğiz. Bu yüzden Text dosyadaki Id ile Kisi tablosundaki Id kolonunu eşleştiriyoruz. Kisi tablosundaki diğer değerleri de seçiyoruz. Bu değerler update işlemi yapılıp yapılmayacağını belirlememizde rol oynayacaklar. Lookup içerisinde General sekmesinde "Specify how to handle rows with no matching entries" altında "Redirect rows to no match output"u seçiyoruz. (SSIS 2005 ile çalışıyorsanız Error output sekmesinde Error kısmı için "Redirect row" seçeneğini seçmelisiniz.)
Lookup sonrası elimizde iki adet muhtemel output oluşacaktır. Bunlardan bir tanesi yeni kayıt gelmesi durumda kullanacağımız "Lookup No Match Output" ve diğeri de güncellenecek olan kayıtlar için kullanacağımız "Lookup Match Output". Gelen kayıtlar yeni ise, bu kayıtları SQL Server'daki Kisi tablomuza insert edeceğiz. Bu amaçla OLE DB Command transformation kullanıyoruz. (OLE DB Destination da kullanabiliriz)
Component Properties sekmesinde en altta bulunan SqlCommand kısmına gerekli insert sorgusunu yukarıdaki gibi yazıyoruz. Daha sonra Column Mappings sekmesine geliyoruz ve gerekli eşleştirmeleri yapıyoruz. Bu tarafta önemli olan nokta, doğru sırada eşleştirmeleri yapmak. Bir önceki adımdaki insert sorgusunda bulunan soru işaretleri, değerlerin hangi sırada gireceğini belirlemektedir.
Bir sonraki adımda lookup işlemine geri dönüp bu sefer de varolan kayıtlarlar ilgili olan işlemleri gerçekleştiriyoruz. Burada elimizde iki senaryo mevcut. Birincisi text dosyadaki bir satır ile Kisi tablosundaki bir satırın birebir eşleşmesi durumu ve ikincisi text dosyada güncel bir satır olması durumu. Bu işlemleri ayırabilmek için tekrar Conditional Split Transformation kullanıyoruz.
Güncellenecek kayıtlar için tekrar OLE DB Command transformation kullanıyoruz. Bu sefer bir update işlemi gerçekleştireceğiz.
Insert işlemine çok benzer bir şekilde update işlemini de gerçekleştiriyoruz. Yine dikkat edilmesi gereken taraf ? lerinin eşleştirilme işlemi. Yapılacak yanlış eşleştirme paketimizin sağlıklı çalışmasını engelleyecektir. Özellikle de kolonlarımızın veri tipleri aynı ise SSIS bunun hatalı olduğunu algılamaz. Dolayısıyla birkaç kere test edip tablomuzu kontrol etmek yararlı olacaktır.
Birebir aynı kayıt gelmesi durumunda işlem yapmama seçeneğini seçebiliriz. Ben bu adımda, birebir aynı kayıt geldiğini görebilmek için text dosyaya yazdırma işlemi gerçekleştirdim ancak bu kısım test aşaması hariç çok da önemli değil.
4. Paketimizin Son Hali ve Çalışması Durumu ile Paketin Çalışması Sonrası Kisi Tablosunun Durumu ve Hatalı Değerlerin Olduğu Text Dosyası
Güncellenmiş Kisi Tablosu
Hatalıları İçeren Text Dosya
