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
