SSIS (SQL Server Integration Services)
SSIS
bir veri kaynağından (Veritabanı,Dosya vs..) verilerin
alınıp arada istenirse işlemlere tabi tutularak
başka
bir veri kaynağına aktarılmasıdır. Biz bunu
yaparken Visul Studio 2005 kullanacagiz.
Öncelikle
konuyu iyi kavrayabilmek için bir senaryo yazalım. Onun
üzerine projemizi yapalım.
Soru : İçindeki
dataları aynı olan fakat bazen birinden fazla diğerinde
eksik veya tersi durumların olduğu İki farklı
veritabanı arasında tablo bazlı senkornizasyon işlemi
nasıl yapılır ?
Öncelik soru biraz
karışık gözüktüğü için
tesbit yapalım.
Local_db
ve Remote_db iki veritabanı. Bu veritabanlarının
localde bir yerde olduğunu varsayıyoruz.
A1 ve B1
tabloları için mevcut datalar aşağıdaki
gibidir.
Local_db
.A1 Remote_db .A1
1 1
2 2
7 5
8
9
Bunların
eşitenmesi için A1 deki fazla datanın B1’ e,
B1 deki fazlaların (varsa) A1’e geçilmesi
gerekiyor.
Burdaki veritabanları farklı serverlarda
bulunuyor. Bunlara ulaşmak için Linked
Server (Daha önce
anlatmıştık.) kullanabiliriz. O zaman bu işlem
için şu şekilde bir Sql cümleciği yazmamız
gerekicekti.
Select
Ldb.*,Rdb.*
From local_db.dbo.A
Ldb
left
join remote_db.dbo.A
Rdb ON Rdb.PrID
= Ldb.PrID
Yukardaki
sorgu bize şöyle bir sonuç verecektir.
Bizim
istediğimiz ise Local db de olan fakat remote db de olmayan
dataları Remote Db ’ ye girmek.
Bunun için
Locale A Remote a B dersek. A-(A∩B)
yapiyoruz. (A tablosundan A kesişim B yi çıkartıyoruz,
sadece B’ de olan datalar bize kalıyor.)
Select Ldb.*,Rdb.* From local_db.dbo.A Ldb
left join remote_db.dbo.A Rdb ON Rdb.PrID = Ldb.PrID
WHEre
Rdb.PrID is
null
Görüldüğü üzere B ‘ ye girmemiz gereken datalar ortaya çıktı. Şimdi bunu tam işe yarar hale getiriyoruz.
INSERT INTO Remote_db.A (PrID)
Select Ldb.PrID From local_db.dbo.A Ldb
left join remote_db.dbo.A Rdb ON Rdb.PrID = Ldb.PrID
WHEre Rdb.PrID is null
Çalıştırınca
“(3
row(s) affected)” yazısı çıkıyor.
İşlem başarılı.
Şimdi bu
yaptığımız işlemde yazdığımız
Sql cümleciğini SSIS kullanarak yapacağız.
Önce proje oluşturuluyor.
Control Flow tabındayken ToolBox dan “Data Flow Task” sürüklüyoruz.
Şimdi “ToolBox” daki menü değişmiştir.
Data
Flow Souces,Data Flow Transformations ve Data Flow Destinations
olmak
üzere 3’e ayrılıyor. İşlevleri adından
anlaşılacağı gibidir
İlk iş olarak veritabanı işlemlerinde kullanmak için “Data Sources” ler ekliyoruz. Böylelikle ilerde sadece Data Source ları değiştirerek kolayca db yönlendirmesi yapabiliriz.
Solution
da Data Sources e sağ tıklayıp Yeni Data Source ekle
deyin.
Açılan penceren Tekrar New butonuna tıklayıp Bağlantı tipi ve bilgilerini girin.
Ok ve Finish deyip bitirin.
Aynı bağlantının birde Remote olanını yapın.
Eğer “Data Reader” componentini kullanacaksak bunun için ayrı bir bağlantı daha oluşturmalıyız. Ve bağlantı tipinde .Net Provider \ Sql Client Data Provider seçmeliyiz.
Şimdide
“Connection Managers”ı kullanarak
bağlantılarımızı hazırlayalım.
New
OLE DB Connection ı tıkladıktan sonra çıkan
penceren data soruce lerimizi göreceğiz. Bunları seçip
uygun bağlantıları oluşturuyoruz. Hem Local_db
hem Remote_db için bunu yapıyoruz.
Ayrıca Eğer Data Reader kullanacaksak onun bağlantısını da oluşturmalıyız. Connection manager da sağ tıklayıp / New Connection from Data Source diyoruz ve Ekrandan Sql Client adını verdiğimizi seçiyoruz.
Bağlantılarımızı yaptığımıza göre şimdi işlemlere geçebiliriz.
“Data
Flow” tabına geçip “Data Flow
Souces /OLE DB Source” sürükleyip
bırakıyoruz.
zOLE
Db üzerine sağ tıklayıp “Edit”
diyoruz.
Ok’ e basip çıktığımızda
üzerindeki kırmızı çarpı işareti
kayboluyorsa işlem doğru demektir.
Şimdi ikinci
Ole DB Data Sourcue mizi olusturup Remote Db seçiyoruz.
2 adet Data Flow Transformations/Sort u sürükleyip ekrana bırakıyoruz.
Ole Db Source Local ve Ole DB Remote dan çıkan yeşil okları altındaki sortlara BAĞLIYORUZ.
Sort’
un üzerine sağ tıklayıp Edite basıyoruz.
Ekranda tablomuzdaki alanlar belirecektir.
Join atmak istediğimiz
alanları seçiyoruz.Seçili alan aşağıda
belirecektir. Sıralama tipini de değiştirebilriiz.
Eğer
tekrar eden kayıtlar varsa ve onlardan kurtulmak istiyorsak en
alttaki yazının karşısını
tıklıyoruz.
Ok’ butonuna basıp çıkıyoruz.
Diğer sortuda yukardaki gibi yapıyoruz.
Data
Flow Transformations/ Merge Join Componentini
alıp ekrana bırakıyoruz.
Sortlardan çıkan
yeşil okları Merge’nin üzerine bırakın.
Oku bıraktığınız an size hangi inputu
aldığını sorar. (hangisi sağ hangisi
sol)Birini seçip Ok deyin. Diğerinde otomatikmen diğer
seçenek seçilir.
Merge Join comonentine sağ
tıklayıp Edit deyin.
Ekranda Sort componentinden gelen
datalar join olarak işaretli gözükecektir. Yanlarında
ki kutucukları işaretlerseniz onlarda çktı
olarak gözükücektir. En tepede “Join Type”
bulunmakta burdan birleştirme türünü seçeceğiz.
Bizim yaptığımız “Left Join” bu
nedenle uygun olanı seçiyoruz.
Şimdi
işlem sonrasında elde ettiğimiz datalar için
“WHERE” kısmımızı yazalım.Bunun
için
Data Flow Transformations /Conditional Split Componentini ekrana bırakıyoruz. Merge’den çıkan yeşil oku buna bağlıyoruz. Üzerine sağ tıklayıp Edite bastıktan sonra karşımıza çıkan ekrandan ;
Ekran 3 parçaya
ayrılmış. Üst solda değişkenlerimiz
yani kolonlarımız sağda kullanabileceğimiz
methodlar mevcut. Aşağıda da kullanım alanı
ve koşullar.
Conditional Split
Componentini tamamen koşul işlemleri için
yapılmıştır.
Configure Error Output butonu
ile koşul işlemi sonucunda hataları igone edebilir
veya açabiliriz.
Not:
Koşul işlemlerinde iki alan karışlaştırırken
alanlardan biri “null” olursa hata alırsınız
bunun için convert methodlarını kullanabilirsiniz
ama sorun çıkabiliyor. Bu yüzden null ları
durdurmak için OLE DB ile tablodaki dataları alırken
sql ile çağırıp null gelecek alanları ‘0’
a convert edebilirsiniz.
Örnek: se
select ISNULL(ID,
'0') AS
ID,isnull(A1,'0')
as A1 From
A
Data Flow
Destinations/ OLE Db Destination Componenti
Burda
elde ettiğimiz datayı komple A nın tempine
aktarıyoruz.
Bu haliyle Paketimiz çalışacaktır. Ekran görüntüsü ;
Aslında istesek doğrudan Remote_db nin A tablosuan yazabilirdik ama burda maksat SSIS araçlarını daha fazla kullanmak ve tanımak.
Şimdi INSERT
ettiğimiz dataları ait oldukları yere itmek gerekiyor.
Bunun içinde bir Sp yazıp paketimizde bu sp yi
çalıştıralım.
Sp ;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [SSIS_INS_A]
as
INSERT INTO A
(
PrID
)
Select
atmp.PrID
FROM A_temp atmp(NOLOCK)
LEFT OUTER JOIN A (NOLOCK) on atmp.PrID = A.PrID
WHERE
A.ID Is NULL
Bu Sp yi çalıştırmak için ;
Control Flow tabına gelip “Execute SQL Task” Componentini ekliyoruz. Data Flow Task dan çıkan oku buna ekliyoruz.
Sql Statement yazan yere SP adını yazp Ok diyoruz.
Unutmamamız gereken birşey var oda Bu işlem her gerçekleştiğinde temp tablonun içinin boşaltılması gerektiği. Bunun için Bir sql task editörüde en başa ekliyoruz.
İçine : TRUNCATE TABLE [A_temp] komutunu yazıyoruz.
Son olarak yaptığımız işin olup olmadığının email olarak gelmesini ve zamanını öğrenelim.
ToolBox’ dan “Send Mail Task” Componentini sürükleyip bırakıyoruz. Ayarları yapmadan önce ekranda boş bir yere gelip sağ tıklayın ;
Variables I tıklayıp
aılan penceren Add Variable diyerek yeni bir değişken
yaratıyoruz.
Tipini string seçip üzerindeyken F4 e basın. Böylece Variables in Property sine gireceksiniz.
EvaluateAsExpression değerini True yapın.
Expression a tıklayın;
Buraya aşağıdaki değerleri girin ;
"\nBaslama Saati : " + (DT_STR, 30, 1252) @[System::StartTime] +
" \nBitis Saati : " + (DT_WSTR, 30) (DT_DATE) GETDATE()+
" \nToplam Dakika: " + (DT_STR, 30, 1252) DATEDIFF( "mi", @[System::StartTime],GETDATE() )
+ " \n"
Evaluate Expression butonuna basıp yazıdğımızın çalışıp çalışmadığını görebilirsiniz.
Böylece değişken oluşturma işide tamamlanmış oluyor. Şimdi sıra bu değerleri email ile yollamaya geldi.
Componentin üzerine
sağ tıklayıp sırayla ilgili ayarları
yapıyoruz.
Değişkeni mail içine gömmek için Expressions tabındaki ayar kısmına tıklıyoruz açılan penceren
Message Source
Propertysinin expressionuna tıklayıp içine
değişkenler listesine bizim daha önce kullandığımız
değişkeni ekliyoruz. Sürükle bırak
yapabilirsiniz.
Expression : "A paketi basariyla calisti \n" + @[User::TotalExecutionTime]
Şimdi herşey tamam ama email gitmiyorsa localda smtp serverini açıp şu ayarı yapın ;
Access / Relay / Add / single Computer e ip yazin / Ok / Ok / Apply
Herşey güzel
olduda paketi başka bir yere taşıdığınızda
Güvenlik nedenleriyle çalışmayabilir.
O
yüzden paketinize şifre koyarsanız böyle
sorunlarla karşılaşmazsınız.
Şifre
koymak için ;
Paketin özelliklerinden Security tabında
/ Protection Level = EncryptAllWithPassword yapın.
Birde şifre yazarsanız işlem tamam olacaktır. Paketin son hali ;
Volkan
Altan
Http://volkanaltan.blogspot.com
Uygulama Link: http://www.volkanaltan.com/Dokumanlar/ssis_kullanmak.zip
Güncelleme olursa : http://docs.google.com/Doc?id=dgndh2qr_51f9t29tc7
Paket Şifresi : 123456