SQL SERVER INTEGRATING SERVICES (ssis)BİR KLASÖR İÇİNDEKİ BÜTÜN EXCEL DOSYALARINI TARAYIP VERİTABANINA KAYDETMEK
Merhaba, SSIS ya da uzun deyimiyle Sql Server Integrating Services sqlserver 2005 ile gelmiş bir servistir. Ne işe yarar sorusunun cevabı ise aslında oldukça geniştir. Ama özetle her tip veri kaynağından ki bunlar OLEDB dediğimiz Sqlserver, Access gibi veri tabanı yazılımları olabileceği gibi bir Excel dosyası, düz bir metin dosyası, xml hatta bir web servisi de olabilir, her tip veri kaynağına data aktarabilmemizi sağlar. Hatta bu aktarım sırasında veriler üzerinde de istediğiniz değişiklikleri yapabilirsiniz. Mesela tablonun kolonları üzerinde her türlü işlemleri yapabilir, Stored procedure çağırabilir, dosyalar-klasörler oluşturabilirsiniz. Amacınız farklı kaynaklardaki verilerin entegrasyonu, birleştirilmesi ise ssis servisi sizin için biçilmiş kaftandır. Üstelik bütün bu işlemleri kod dahi yazmadan yapabilirsiniz.
Ssis’in kullanım alanları tabi ki çok geniştir. Ama fikir vermek açısından iş hayatındaki gerek duyulduğu durumlardan birkaç örnek verelim. Bir müşteriniz var verilerini Excel dosyalarında tutuyor. Daha sonra işi büyüyor ve karmaşıklaşıyor. Dolayısıyla Excel artık ona yetmiyor ve sizden işlerini yoluna koyacak bir program istiyor. İşte bu durumda Excel dosyalarını nasıl sisteminize alacaksanız. Belki yıllarca veri var içinde. Başka bir durum daha örneğin merkez bankasının döviz kur değerlerini tarihleriyle birlikte bir veritabanına aktarmak istiyorsunuz. Bu değerler her gün web sitesinden xml dosyası olarak yayınlanıyor olsun. İşte böyle bir durumda da ssis özelliklerini kullanarak istediğiniz kadar tarihe ait veriyi(belki son 10 yıllık) web sitesinde yayınlanan xml’den alıp veritabanınıza alabilirsiniz. Bu örnekleri çoğaltmak mümkün ama sizi daha fazla sıkmadan konuya geçmek istiyorum.
Bir klasörünüz var içinde aynı tip veriler içeren Excel dosyaları var. İşte bir package oluşturup(ssis projelerine package denir.) bu verilerin otomatik okunup bir sqlserver 2005 tablosuna yazdırılmasını istiyoruz. (Önemli Not: ssis servisi sqlserver 2005 ile gelen bir özelliktir. Bu servis daha önceki sql versiyonunda DTS[Data Tronsformation Services]olarak geçmekteydi.) Örneği aşama aşama yapacağız. Böylece anlamak daha kolay olacaktır. Örneğe başlamadan önce ssis servisini kullanmak için sqlserver kurulumuyla birlikte kurulmuş olması gerektiğini hatırlatmak isterim. Eğer kurulu değilse sqlserver 2005 cd’sini kullanarak bu bileşeni ekleyebilirsiniz. Ben örnekte sqlserver’in devoloper edition’ını kullandım.
1. C: dizini içine ssis_excel isimli bir klasör oluşturun.
2. Bu dizin içinde 3 tane Excel dosyası oluşturun. Bu Excel dosyalarını istediğiniz gibi isimlendirebilirsiniz ama içleri ortak yapıda olsun. Örnek bir Excel dosyasına ait resim şöyle olabilir.
3. Excel dosyasında fark ettiğiniz üzere ilk satır sütun adı gibi kullanılmıştır ve bu ad ve soyad yazan hücreler diğer Excellerde de ortaktır.
4. Sıradaki aşama ise sql server içinde Excel verilerini biriktireceğimiz tabloyu oluşturmak. Bunun için sql server’ı çalıştırın ve kullandığınız bir veritabanında isimler adlı resimdeki tabloyu oluşturun.
5. Bu hazırlıkları yaptıktan sonra integrating services uygulamamızı başlatalım bunun için visual studio 2005’i açın File --> New --> Project yolunu kullanarak proje oluşturma sayfasını açın. Buradan Bussiness Intellegence Projects altındaki Integration Services Project seçeneğini seçin. Eğer bu seçenek gözükmüyorsa bilin ki ssis özelliği sql server kurulurken kurulmamıştır. Yoksa uygun sqlserver sürümünü kullanarak bu özelliği de ekleyin. Sqlserver’ın devoloper edition sürümünde ssis servisleri bulunmakta.(Bu makalede kullanılmıyor ama çok yararlı SSAS ve SSRS servislerini de kurabilirsiniz.)
6. İsteğinize göre bir isim verip projeyi açın.
7. Ekranın altında bulunan Connection Managers paneli üzerinde sağ tıklayarak New Connection komutunu verin. Açılan pencereden Excel seçeneğini seçin ve Add…
düğmesine tıklayın.
8. Açılan ekranda Browse düğmesine tıklayarak c:\ssis_excell klasöründeki bir excel dosyasını gösterin. Aslında burada hangi excel dosyasını gösterdiğiniz önemli değildir. Çünkü bunu ssis’in bir değişkenden okumasını isteyeceğiz. Bunu yapmamızın sebebi değişkeni oluşturana kadar ssis’in hata vermesini engellemek. Connection Managers sekmesi altına bağlantı gelecektir.
9. Şimdi visual studio’nun toolbox panelini görüntüleyin. Alışmış olduğuğunuz toolbox’dan farklı değil mi? Çünkü üzerinde sadece ssis ile ilgili araçları bulundurmakta. Bu panelden Data Flow Task aracını sürükleyerek ekrana taşıyın. Bu araç farklı tipteki veri kaynakları arasında dönüşümü sağlayan ve ssis’in sanıyorum en sık kullanılan bileşenidir. Data Flow Task’in üzerine çift tıklayarak içine girin. İçine girdiğiniz zaman toolbox üzerindeki araçların değiştiğini göreceksiniz. Bu durumda araçlar 3 kısma ayrılmıştır. Source, Transformation ve Destination araçları. Adlarından anlaşılabileceği üzere bu araçlar, veriyi alan (source), işleyen(transformation) ve kaydeden(destination) araçlardır.
10. Şimdi ToolBox’ın Data Flow Sources kısmından bir Excel source’u alıp ortaya sürükleyin. Hemen sonrada Data Flow Destinations kısmından bir OleDB destination alın ve gene ortaya sürükleyin.
11. Excel Source kutusunun kenarına çift tıklayarak özelliklerine ulaşın. OLE DB Connection Manager alanından Excel Connection Manager’ı seçin. Hatırlarsanız ki bu daha önceden Connection Managers sekmesinde oluşturduğumuz bağlantı idi. Daha sonra Data Access mode kısmından Table or view ve Name of Excel Sheet kısmından ise sayfa1’i seçin. Tabi İngilizce bir Excel kullanıyorsanız burası sheet1 şeklinde olacaktır. Son görüntü bu şekilde:
12. Excel source kutusundan çıkan yeşil oku sürükleyerek OLE DB Destination kutusuna bağlayın.
13. Sırada destination bağlantılarını yapmak var. OLE DB destination kutusunun kenarına çift tıklayarak özelliklerini açın. New düğmesine tıklayın ve açılan pencerede yeniden New düğmesine tıklayın. Çıkan pencerede sizin sql server’ınıza ve isimler adlı tablonuzun bulunduğu veritabanına bağlantı yapın. Görüntü benzer şekilde olacaktır. İsterseniz Test Connection düğmesini tıklayarak bağlantınızı sınayabilirsiniz.
14. 2 defa ok düğmesine tıklayarak bu pencereleri kapatın. Data Access mode alanından Table or View seçeneğini Name of the table or the view kısmından ise isimler adlı tabloyu seçin. Sonra OK diyerek bu pencereyi kapatın.
15. Şimdi bir visual studio programı gibi package’ı yani çalışmanızı çalıştırın. Kutular yeşile döndüyse package çalışmıştır. Sqlserver içinde isimler tablosuna bakın. Seçtiğiniz Excel tablosundaki veriler yazılmış değil mi?
16. Bir Excel dosyasını ssis servisi ile sql server’a geçirmeyi başardınız. Şimdi sıradaki aşamaya hazırsınız. Belirttiğimiz klasördeki bütün Excelleri tarayıp sql’e geçirmek. Ben yaşamadım ama hata alıyorsanız belki Excel dosyanız açık olabilir. Onu kapatıp bir daha deneyin.
17. Connection Managers alanındaki Excel Connection Manager’i seçin ekranın sağındaki Properties penceresininden Connection String değerini kopyalayın. Önemli bir nokta Excel connection manager üzerine çift tıklarsanız, ilgili edit penceresi gelir. Properties penceresi ekranın sağındaki alışageldiğimiz properties penceresi. Buradan kopyaladığınız değer yaklaşık şöyle bir metin olmalı.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ssis_excel\Kitap1.xls;Extended Properties="Excel 8.0;HDR=YES";
18. Şimdi ekrandaki sekmelerden Control Flow sekmesine tıklayın
19. Bu alanda boş bir yere tıklayın. Şimdi bir variable tanımlama vakti. Bunun için View --> Other Windoww --> Variables yolunu kullanarak Variables sekmesini görüntüleyin. Bu sekme üzerinde Add Variable düğmesine tıklayarak filename isimli bir değişken oluşturun değişken tipini de string olarak değiştirin. Son olarak connection string’den kopyaladığınız değeri sadece dosya yolunu gösterecek şekilde buraya yapıştırın. Oluşturduğunuz değişkenin scope değerinin package olmasına özen gösterin. Böylece bütün proje içinde geçerli olacaktır. Eğer package değilse Control Flow alanında boş bir yere tıklayarak değişkeni yeniden eklemeyi deneyin. Son görünüm şöyle olacaktır.
20. Excel Connection Manager’in properties sekmesindeki Expressions alanının yanındaki düğmeye tıklayın. Property Expressions Editor penceresi açılacaktır. Property alanından Connection String’i seçin ve yanındaki Expressions düğmesini tıklayın.
21. Ekrana "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::filename] + ";Extended Properties=\"Excel 8.0;HDR=YES\";" şeklinde yazın. Fark edeceğiniz üzere kopyaladığımız connection string’i, data source değerini değişkenden alacak şekilde değiştirdik.
22. Burada expression’ın doğru yazılması çok önemlidir. OK düğmelerine tıklayarak pencereleri kapatın. Projenizi bir daha çalıştırabilirsiniz. Gene belirttiğiniz Excel dosyasını veritabanına yazacaktır. Henüz klasör içindeki bütün dosyaları taratmadık.
23. Control Flow sekmesine gelin ve toolbox’dan bir tane Foreach Loop Container’ıData Flow Task öğesini alıp container’ın içine sürükleyerek koyun. sürükleyip çalışma alanına ekleyin daha sonra da
24. Foreach Loop Container kenarına çift tıklayarak özelliklerine erişin. Collection sekmesinden enumerator özelliğini Foreach File Enumerator olarak ayarlayın. Foreach File Enumerator, loop container’ın her dosyayı aramasını sağlar. Tabi bir de dosyaları nerede araması gerektiğini belirtmek gerekir. Browse düğmesini kullanarak içinde Excel dosyalarının bulunduğu klasörü seçin. Files alanına *.xls yazarak belirtilen klasörde sadece excel dosyalarını taratmayı seçin. Bu alanı geçmeden önemli bir noktaya değinmek istiyorum. Mesela dosya01.01.2008.xls, dosya02.01.2008.xls şeklinde Excel dosyalarınızı almak istiyorsanız buraya dosya*.xls yazabilirsiniz. Böylece adı dosya ile başlamayan excel’ler dikkate alınmaz. Son görünüm şu şekildedir:
25. Şimdi aynı pencerede Variable Mappings sekmesine tıklayın.Buradan da değişkeninizi seçip OK düğmesine tıklayın.
26. İşte package’iniz hazır. Baştan çalıştırdığınızda bütün Excel dosyalarının taranıp veritabanına yazıldığını göreceksiniz.
Oldukça etkileyici değil mi? Umarım basamak basamak incelediğimiz bu örnek sorunlarınızı çözmeye yardımcı olur.
