Microsoft SQL Server Integration Services (SSIS) – 2

SCRIPT TASK

SSIS te hazır bulunan tasklar kullanılarak yapılamayan işler için “Script Task” kullanılabilir. C# veya VB.Net script dilleri kullanılabilir. “Script task” ta “Edit Script” çift tıklandığında Microsoft Visual Studio Tools for Applications (VSTA) ekranı açılır. Standart gerekli kod ekranda görüntülenir. “Main” fonksiyonu içerisine istenilen kod yazılabilir. Kod parçaları ayrı metodlarda yazılabilir. Debug için F9 tuşu ile istenen yere/yerlere breakpoint konulduktan sonra VSTA kapatılır. “Script Task” ekranı kapatılır. F5 ile paket çalıştırılır. “Script Task” çalışmaya başladığında sıra breakpoint konulan satıra geldiğinde VSTA açılarak kürsör satır üzerinde durur. F10 veya F11 ile adım adım ilerlenerek, Local, Watch, Immadiate pencereleri kullanılarak kod debug edilir.

Scriptte kullanılacak değişkenler kullanım amaçlarına göre ReadOnly veya ReadWrite variables kısmına eklenmelidir.

FOREACH LOOP CONTAINER

Bir grup veri üzerinde adım adım ilerlemeyi sağlar. Grup verisi farklı tipte olabilir. Örneğin bir klasördeki dosyaların adların ile işlem yapmak isteniyorsa çift tıklanıp özelliklerine girilip Collection ekranından Enumerator özelliği “Foreach File Enumerator” seçilmelidir.

Buradan dosya listesi getirilecek klasör ve döndürülecek dosya listesi için Files seçeneği vardır. Tüm dosyalar “*.*” ile örneğin txt dosyaları için “*.txt” yazılmalıdır. Retrieve file name grubunda, “Fullly qualified” seçilirse path + dosya adı ve uzantısı, diğerlerinde dosya adı ve uzantısı ve sadece dosya adı gelecektir. “Travers subfolders” işaretlenirse alt klasörlerdeki dosyalar da gelecektir.

“D:\Sql\BI\KaynakDosya\Aktarılmayan” klasöründeki tüm text dosyalardaki veriler bir foreach loop container içinde DW a aktarılacaktır.

“Variables Mappings” ekranında dönen dosya isimleri bir değişkene atanabilir.

Bu şekilde DosyaAdi değişkenine aktarılan dosya adlar her bir döngüde Dosya Aktar görevinde kullanılan “Musteri Text Connection” un ConnectionString parametresine Expression ile atanmalıdır.

Döngü içinde aktarılan her dosya bir “File System Task” ile “Aktarılanlar” klasörüne move edilmelidir.

ACTIVEX SCRIPT TASK

VB Script veya Jscript yazılarak istenilen işlemin yapılması sağlanır. Sql Server 2000 DTS lerinde script yazmanın tek yolu ActiveX Script Task kullanmaktı. Ancak SSIS ile çok daha gelişmiş “Script Task”  görevi sunulmuştur. Dolayısyla artık “ActiveX Script Task” ı yazmak anlamsız hale gelmiştir.  Ancak Sql 2000 den paket upgrade yapıldıysa oradaki scriptlerle ilgili işlemlerde kullanılabilir.

ANALYSIS SERVICES PROCESSING TASK

Bir Analiz servisindeki tüm OLAP nesnelerinin(Küpler, KPI lar, Mining modelleri) veya seçilen bazı nesnelerin process edilmeleri için kullanılır. Process etmek DW daki verilerin nesnelere doldurulması ve bu aşamada gereken hesaplamaların yapılmasına denir.

EXECUTE PACKAGE TASK

SQL Serverde veya File sistemde bulunan bir dtsx paketinin çalıştırılması için kullanılır.

Paketin bulunduğu yer(Location)(Sql Server veya File Sistem) seçilip, ilgili bağlantı yaratıldıktan sonra paketin adı ve varsa Password ü verilmelidir.

EXECUTE PROCESS TASK

Çalıştırılabilir bir dosyanın(.exe, .bat, .cmd)  uzantılı dosyalar) çalıştırılması için kullanılır.

FTP TASK

Bir ftp server e bağlanıp dosya alış verişi yapılmasını sağlar.

Özelliklerine girildiğinde General ekranında bir ftp bağlantısı seçilmesi istenir. Yeni bir bağlantı eklenmesi için şu ekran gelir:

Burada ftp serverin ip adresi veya dns adresi ve portu verilmelidir. Ayrıca kullanıcı adı şifresi vs verilebilir.

Bunun  dışında File Transfer ekranında Local ve Remote daki klasörler veya dosyalar belirtilmelidir. Bunlar değişkenlreden de gelebilir. Operation özelliğine yapılacak işlem verilmelidir. İşlem veri alma veya gönderme, klasör yaratma, silme veya dosya silme olabilir.

SEND SMS TASK

Mail göndermeye yarar.

Özelliklerine girildiğinde General ekranında bir smtp bağlantısı seçilmesi istenir. Yeni bir bağlantı eklenmesi için şu ekran gelir:

Burada Server adı veya dns adı girilir. Mail ekranında ise gönderilecek mailin kimden, kime gideceği, kime cc veya bcc leneceği ve konusu seçilir.  MessageSourceType de çalıştırılacak sql cümlesinin elle mi girileceği(Direct Input), bir değişkenden mi alınacağı(Variable) veya bir dosyadan mı alınacağı(File Connection) seçenekleri vardır. Seçime göre bir altındaki MessageSource seçeneğinde Direct input için bir Mesaj cümlesi istenecektir;  File Connection için Mesajın bulunduğu dosyanın bağlantısı, Variable için ise için ise gönderilecek mesajın bulunduğu değişkenin adı istenecektir.

WEB SERVICE TASK

Bir web servisinde istenen metodun çağırılarak, dönen sonucun bir dosyaya veya değişkene yazılması için kullanılır.

XML TASK

Bir xml dosyasının veya elle girilen veya değişkende bulunan XML in geçerlilik denetimini yapmak için veya iki xml in karşılaştırılması, birleştirilmesi(merge) vb. İşlemler için kullanılır.

DÖNÜŞÜMLER(TRANSFORMATIONS)

Data Flow Task içerisinde veri kaynağından(Data Source) veri alındıktan sonra bir hedefe((Data Destination) yazılmadan önce veri üzerinde çeşitli değişiklikler, eklemeler, dönüşümler vs. yapılması gerekebilir. Transformasyonlar bunun için kullanılır. Yapılmak istenen transformasyon işlemi sürüklenip Data Flow ekranın üzerine bırakılır. Daha sonra Data Source dan yeşil ok seçilip sürüklenerek transformasyon işlemi üzerine bırakılır. Seçilen transformasyon tipine göre gerekli işlemler yapılıp, transformasyon işleminin yeşil oku tıklanarak hedef üzerine bırakılır. Kaynak ve hedef veri arasında birden fazla transformasyon işlemi olabilir. Bunlar oklarla birbirlerine bağlanmalı ve tüm işlem kaynaktan hedefe kadar bağlanmış olmalıdır.

Bir text veri kaynağından veri alıp, birden fazla hedefe veri yazmak için veri üzerinde çoklama(multicast) yapan sonra çoklanan veriyi bir  OleDb hedefine ve üzerinde türetilmiş kolon(Derived Column) ve Özetleme(Aggregation) yaparak başka bir OleDb hedefine yazan Data Flow Taskı şöyledir:

AGGRERATE

Veri üzerinde gruplama ve özetleme işlemi yapar. Yapılabilecek işlemler şunlardır:

1. Gruplama(Group by)
2. Kayıt adedi sayma(Count)
3. Farklı Kayıtların adedini sayma(Distinct Count)
4. Minimum değeri bulma(Minimum)
5. Maximum değeri bulma(Maximum)

Gelen verideki Date isimli kolon üzerinde gruplama yapan ve bunu TarihGruplari olarak  üreten özetleme dönüşümü şöyledir:

DENETLEME(AUDIT)

Pakete veya Task a ilişkin bir takım verilerin oluşturulup bir veri kaynağına yazılmasını sağlar. Denetleme verisi, kaynak veriye ek bir bilgi olarak verinin aktarıldığı hedefe konulabileceği gibi tamamen farklı sadece denetleme amacıyla tutulan farklı bir hedefe de yazılabilir.

Paketin adını ve çalışma zamanını oluşturan bir denetleme dönüşümü şöyledir.

KOLON KOPYALAMA(COPY COLUMN)

Veri kaynağındaki bir veya daha fazla kolonun kopyasını çıkartır. Yeni kolon/kolonlar daha sonra üzerinde dönüşümler yapılarak veya doğrudan veri hedefine yazılırlar. Kaynaktaki bir kolon, hedefte sadece bir kolona yazılabilir. Örneğin Price kolonunu hedefte hem Fiyat hem de ToplamFiyat kolonlarına yazabilmek için kaynaktaki Price kolonundan, bir de ToplamFiyat olarak kolon oluşturmak için:

DATA CONVERSION

Kolonlar üzerinde veri tipi değişiklikleri yapar. Unicode veri tipindeki Eposta ve Adi kolonlarını Stringe çevirip EPostaYeni ve AdiYeni isminde bir kolonlar oluşturmak için:

LOOKUP

Bir veri seti içinde bir değeri bulup aynı satırdaki diğer bir veya daha fazla değeri döndürmek için kullanılır.

Müşterileri DW ya aktarırken farklı kaynaklardan aynı numaralı müşteriler geldiği için DW da müşterilere farklı bir MusteriNo verilmektedir. Orijinal MusteriNo su Original_PK, Kaynak veritabanı adı da Kaynak_DB kolonlarında tutulmaktadır. Müşterilerin faturaları aktarılırken kaynak veritabanlarındaki MusteriNo yu DW da verilen MusteriNo ile değiştirmek için Lookup dönüşümünü kullanalım. Bunun için DW daki dimMusteri tablosundaki veriler kullanılmalıdır. Veriyi tamamen cache e alıp daha hızlı sonuç döndürmesini sağlayalım. Partial Cache de sadece süreç içinde bulunan veriler cache içine alınacaktır.

Veri kaynağını Connection kısmından DW seçip kullanılacak sonuç setini fazlalık kolonları alıp memory harcamamak içinsorgu ile sadece gerekli kolonları alalım.

Columns kısmında eşleşecek kolonları birbirine bağlayıp, dönecek kolona da check işareti konulmalıdır. Biz burada Fatura daki MusteriNo kolonunu lookup verisindeki MusteriNo kolonuyla, Fatura daki KaynakVeritabanı kolonunu lookup verisindeki KaynakDB kolonuyla eşleştirip bulunan MusteriNo yu döndürüyoruz.

MERGE

Birden fazla sonuç setini birleştirip tek sonuç seti oluşturmak için kullanılır. Yani her iki kaynaktaki satırlar birleştirilecek, oluşacak sonuç setindeki satır sayısı iki kaynaktaki satır sayılarının toplamı olacaktır. Sonuç setleri sıralanmış olmalıdır. Bunun için sonuç setlerinin üzerinde sağ Mouse tuşuyla tıklayıp ‘Advanced Editor’ seçilmelidir. Burada “Inputand  Output Properties” seçilerek “OleDb Source Editor” tıklanarak “IsSorted” true yapılmalı,

Sonrasında “Output Columns” kısmında hangi kolona göre sıralanacağı seçilip “SortKey position” 1 yapılmalıdır. Bu kolndaki verilerin aynı olması durumunda 2. Sırlam kolonu seçilmeli ve “SortKey position” 2 yapılmalıdır, bu şekilde başka kolonlar da seçilebilir.

Bundan sonra Merge işlemi çift tıklanıp, iki kaynaktan kolonlar karşılıklı olarak seçilmelidir. Merge işleminden sadece bu kolonlar çıkacaktır.

MERGE JOIN

Birden fazla sonuç setindeki kolonları birleştirip tek sonuç seti oluşturmak için kullanılır. Yani her iki kaynaktaki kolonlar birleştirilecektir. Bu Sql dilindeki join gibidir. Veri aynı veri kaynağından geliyorsa orada join yapılarak döndürülebilir. Yani bu işleme gerek yoktur. Ancak farklı kaynaklardan geliyorsa çözüm merge joindir. Bu işlem için de sonuç setleri sıralanmış olmalıdır.

Örneğin Müşterilerimiz bir veritabanında, bu müşterilerin bizdeki ilgili personelleri ayrı bir veritabanında olsun. Bunları MusteriIlgiliPersonel adında aşağıdaki gibi bir tabloya aktarmamız gerekirse

create table MusteriIlgiliPersonel
(
MusteriNo int not null,
MusteriAdi varchar(100),
PersonelAdi varchar(100)
)

İki kaynak da Merge işlemindeki gibi sıralanıp Merge Join dönüşümüne eklenmelidir.

Daha sonra Merge Join çift tıklanarak Joinin tipi ve hangi kolon(lar) üzerinden join edileceği kolonları birbirine bağlayarak ve hangi kolonların döndürüleceği checkbox lar ile seçilmelidir. Aynı isimde kolonlar varsa “Output Alias”larında değişiklik otomatik olarak yapılır. İstenen takma ad(alias)lar değiştirilebilir.