Microsoft SQL Server Integration Services (SSIS)

Microsoft SQL Server Integration Services

Microsoft SQL Serverle gelen ETL aracıdır. Bir çok farklı veri kaynağı(SQL Server, Oracle DB2, Access, Excel, Text dosyalar, XML verisi, ODBC aracılığıyla neredeyse tüm veri kaynakları) arasında veri transferi yapılmasını sağlar. BI projelerine temel olacak DW veya DM oluşturmak için SSIS kullanılır.

Hangi kaynaklar arasında, hangi verilerin transfer edileceği ve transfer sırasında ne tür düzenlemelerin yapılacağı SSDT projesinde oluşturulacak paketlerle belirlenir. Bir projede bir veya daha fazla paket olabilir.

SQL SERVER DATA TOOLS (SSDT)

BI projelerini oluşturmak için SQL Server Data Tools-Business Intelligence(SSDT-BI) kullanılır. SSDT, Visual Studio’nun, içinde BI projeleri(SSIS, SSAS ve SSRS projeleri) geliştirmek için özelleştirilmiş kısıtlı bir versiyonudur SSDT-BI internetten indirilip kurulabilir. Eğer makinada daha önce Visual Studio var ise gerekli komponentler Visual Studioya yüklenecektir.

BI projelerimizde kullanacağımız bir data warehouse veritabanı oluşturalım ve gerekli tabloları oluşturalım.

drop database ButeDW
create database ButeDW
use ButeDW
CREATE TABLE dimUlke(
	UlkeKodu char(2) NOT NULL,
	UlkeAdi varchar(100) NOT NULL,
	CONSTRAINT PK_UlkeKodu PRIMARY KEY (UlkeKodu)
)
CREATE TABLE dimSehir(
	PlakaNo int NOT NULL  primary key,,
	SehirAdi varchar(100) NOT NULL,
	UlkeKodu char(2) NULL,
	CONSTRAINT PK_PlakaNo PRIMARY KEY (PlakaNo)
)

--Musteri tablosu
CREATE TABLE dimMusteri(
	MusteriNo int IDENTITY(1,1) NOT NULL,
	MusteriAdi varchar(100) NOT NULL,
	SehirNo int NULL,
	Cinsiyet char(1) NULL,
	EPosta varchar(50) NULL,
	DogumTarihi date NULL,
	KurumsalMi bit NOT NULL,
	Telefon char(10) NULL,
	TcNo char(11) NULL,
	Orijinal_PK int not null,
	Kaynak_DB varchar(32) not null,
	CONSTRAINT PK_MusteriNo PRIMARY KEY CLUSTERED (MusteriNo ASC)
)
CREATE TABLE factFatura(
	FaturaNo int IDENTITY(1,1) NOT NULL,
	Tarih date NOT NULL,
	MusteriNo int NOT NULL,
	Toplam decimal(10, 2) NOT NULL,
	Orijinal_PK int not null,
	Kaynak_DB varchar(32) not null,
CONSTRAINT PK_FaturaNo PRIMARY KEY (FaturaNo)
)

CREATE TABLE factFaturaDetay(
	FaturaDetayNo int IDENTITY(1,1) NOT NULL,
	FaturaNo int NOT NULL,
	UrunKodu char(10) NOT NULL,
	BirimFiyat decimal(10, 2) NOT NULL,
	Adet int NOT NULL,
	Satir_Toplami decimal (10, 2) NOT NULL,
	Orijinal_PK int not null,
	Kaynak_DB varchar(32) not null,
CONSTRAINT PK_FaturaDetayNo PRIMARY KEY (FaturaDetayNo)
)

--Tarih dimension tablosu
create table dimTarih
(
Tarih date not null constraint PK_Tarih primary key,
Yil int not null,
Ceyrek int not null,
Ay int not null,
AyAdi varchar(10) not null,
Gun int not null,
GunAdi varchar(10) not null
)


select datename(month, getdate())
--February
--oturumun(session) dilini Türkçe yapalım
set language Turkish
select datename(month, getdate())
--Şubat

declare @Tarih date = '20000101'
while @Tarih <= '20201231'
begin
	insert dimTarih
	values (@Tarih, year(@Tarih), datepart(QUARTER, @Tarih), month(@Tarih), 
	datename(month, @Tarih), day(@Tarih), datename(WEEKDAY, @Tarih))

	set @Tarih = dateadd(day, 1, @Tarih)
end

PACKAGE (PAKET)

SSIS te veri transferleri ve diğer işlemler proje içerisinde oluşturulan bir veya daha fazla paket içinde organize edilir. Bu paketler daha sonra Sql Serverde job olarak tanımlanıp, bir zamanlamayla(schedule) örneğin günlük olarak belli saatlerde çalıştırılması sağlanır. Paketler bağlantılardan(connections), görevlerden(tasks), değişkenlerden(variables), Eventler Handlerlerden, Öncelik kısıtları (precedence constraint) ndan oluşur. Paketlerde yapılacak işlemler “Control Flow” sekmesi üzerinde Tasklar eklemek suretiyle düzenlenir.

BAĞLANTI (CONNECTION)

Paketlerin içerisinde Connection Manager içerisinde kullanılacak veri kaynaklarına ilişkin bağlantılar tanımlanır. Bağlantılar Connection Manager penceresinde sağ mouse tuşuyla eklenebileceği gibi, Tasklar oluşturulurken eklenebilir. Bağlantı eklenirken önce bağlantı tipi(Connection Type) seçilir. Bağlantı tipleri OLEDB, Excel, ODBC, ADO.NET vs olabilir. Ardından bağlantı tipine göre gerekli ayarlar yapılır. Örneğin Excel veya Flat File için dosyanın disk üzerindeki yeri ve dosyanın adı seçilmelidir. OLEDB için ise önce erişilecek veri kaynağı için Provider seçilir. Örneğin Sql Server için OLEBDB for Sql Server, ORACLE için OLEDB for ORACLE seçilmelidir. Daha sonra seçilen providere göre bağlanılaca server ve/veya veritabanı adı, kullanıcı adı, şifresi vs seçilmelidir.

Sql Servere OLEDB bağlantısı ekleme örneği

TASK (GÖREV)

Paket içinde belli bir işi yapmak üzere özelleştirilmiş bileşenlerdir. Her paketin içinde bir veya daha fazla task bulunabilir. TasklarVisual Studioiçerisinde Toolbox ta listelenir. Toolbox’tan bir task çift tıklanarak veya mouse ile sürüklenerek Paket penceresindeki “Control Flow” sekmesinde boş bir yere bırakılır. Daha sonra task çift tıklanarak veya sağ mouse tuşuyla tıklanıp Edit seçilerek, gerekli özellikleri(properties) yapılandırılmalıdır. Bu özellikler her task tipi için farklıdır. Bu özelliklerden bir kısmı elle yazılarak veya seçilerek sabit olarak verilebileceği gibi değişkenler aracılığıyla da verilebilir.

Örneğin bir Excel dosyasından veri alıp, bir veya daha fazla kolonundaki veri üzerinde değişikliklerin yapan ve veriyi bir Sql Server e yazan Data Flow Task şöyle görünür.

DEĞİŞKEN(VARIABLE)

Tasklardaki bazı özellikler çalışma zamanında dinamik olarak belirlenebilmektedir. Bunun için değişkenler kullanılmaktadır. Değişken ekranı “Control Flow” veya “Data Flow” ekranlarında sağ mouse tuşuyla veya View- Other Windows-Variables ile görüntülenebilir.

OLAYLAR(EVENT) VE EVENT HANDLERS

Paketler olay güdümlü(event driven) çalışırlar. Her yapılan işlem bir eventi tetikler(Paketin çalışmasının başlaması, bitmesi, bir hata oluşması vs). Bu eventler için çeşitli tasklar tanımlanabilir. Örneğin bir hata oluştuğunda ilgili kişiye mail gönderilmesi gibi.

ÖNCELİK KISITLARI (PRECEDENCE CONSTRAINTS)

Tasklar da birbiri ardına çalışmak için Öncelik kısıtları (precedence constraint) kullanılarak birbirine bağlanır. Öncelik kısıtları renkli oklar olarak görünür. Başarılı bir tasktan sonra başka bir task a geçilmesi için iki task yeşil renkli ok ile birbirine bağlanmalıdır. Yine DataFlow tasklarındaki işlemler de Öncelik kısıtları ile biribirine bağlanmalıdır. Öncelik kısıtları ile birbirine bağlanmayan “Control Flow” ve “Data Flow” işleri aynı anda çalışmaya başlarlar.

İFADELER(EXPRESSION)

Tasklarda, Connectionların çeşitli özelliklerini(property), Öncelik kısıtlarını çalışma zamanında düzenlemek için, ayrıca Türetilmiş kolonlarda, Koşula bağlı parçalama dönüşümlerde(Conditional Split Transformation) ve benzeri bir takım dönüştürme işlemlerinde işlemi tanımlamak için kullanılan basit bir kodlamadır.

Örneğin aşağıdaki ekranda:

(DT_STR,1,1254)(Cinsiyet == 1 ? "E" : "K")

İfadesiyle Cinsiyet 1 ise ‘E’, değilse ‘K’ döndürülmüş ve oluşan sonucun da unicode string den string e tür dönüştürmesi sağlanmıştır.

GÖREV TİPLERİ

Tüm görevlerin özellikleri gruplanmıştır. General ekranında temel ayarlar yapılır. Bu ekranda Görevin adı ve açıklaması verilebilir. Her göreve açıklayıcı bir ad verilmesi iyi bir pratiktir. Çünkü çalışma zamanında verilen hatalarda bu isimler kullanılır. Görev isimleri ayrıca “Control Flow” ekranında görev adının üzerine iki kere aralıklı tıklanarak da değiştirilebilir.

DATA FLOW TASK

Çeşitli veri kaynakları arasında verinin transferi için kullanılır. Data Flow Task en temel görevdir. Birçok farklı kaynak arasında veri aktarımı ve aktarılan veri üzerinde işlem yapılabilir. Dolayısıyla çok fazla seçenek ve özellikler barındırır. Bu yüzden bu task için ayrı bir sekme ayrılmıştır.

“Control Flow” ekranına bir Data Flow Task taskı bırakılıp çift tıklandığında Paket ekranında “Data Flow” sekmesine geçilir ve orada Task ın özellikleri ayarlanır. Data Flow Task için en azından bir veri kaynağı(Data Source) ve bir veri hedefi(Data Destination) a gerek vardır. Ayrıca arada veri üzerinde bir takım değişiklikler yapılacaksa bir veya daha fazla “Data Flow Transformation” eklenebilir.

Kaynak Bağlantı(Connection) eklemek için Source Assistant çift tıklanır veya sürüklenip ekrana bırakılır. Burada ya New denilerek yeni bağlantı eklenir veya var olan bağlantılardan istenen seçilir. New denildiğinde bağlantının nasıl ekleneceği daha önce Bağlantı konusunda gösterilmişti.

Sonra bağlantı tipi(Connection Type) seçilir. Bağlantı tipleri SQL Server, Excel, Flat File veya Oracle olabilir. Ardından yan pencereden bu tipte daha önce eklenen bağlantılar görünür, bunlardan biri veya New seçilerek OK seçilirbağlantı tipine göre gerekli ayarlar yapılır. Örneğin Excel veya Flat File için dosyanın disk üzerindeki yeri ve dosyanın adı seçilmelidir. SQL Server veya Oracle için ise önce erişilecek veri kaynağı için Provider seçilir. Örneğin Sql Server için OLEBDB for Sql Server, ORACLE için OLEDB for ORACLE seçilmelidir. Daha sonra seçilen providere göre bağlanılacak server ve/veya veritabanı adı, kullanıcı adı, şifresi vs seçilmelidir.

Bağlantı yapıldıktan sonra bağlantı çift tıklanarak

Burada önce Connection Managers bir önceki adımda ilgili bağlantı yapıldığı için bu bağlantı seçilidir. İstenirse başka bir bağlantı da seçilebilir. Ardından “Data Access Mode” ile verinin ne yöntemle alınacağı seçilir. Seçenekler “Table or view” veya “Sql Command” olabilir. Ayrıca bunlar değişkenden de alınabilir. “Table or view” seçildiğinde bir alttaki seçenekte veri alınacak tablo veya görünüm seçilir. SqlCommand da seçildi ise bir Sql sorgusu yazılacak bir ekran açılır. Transfer edilecek veri tablo veya view daki tüm satırlar veya sorgu sonucu dönecek sonuç setidir. Ardından Columns ekranından aktarılacak kolonlar seçilmelidir. Hedefe yazılmayacak kolonların seçilmesi hata vermez ama performansı düşürür. Hedef Bağlantı(Connection) eklemek için Destination Assistant çift tıklanır veya sürüklenip ekrana bırakılır. Burada ya New denilerek yeni bağlantı eklenir veya var olan bağlantılardan istenen seçilir. Hedef genellikle DW dur.

Ardından Data Sourcedaki yeşil ok sürüklenip hedef üzerine bırakılır, daha sonra hedef çift tıklanarak ayarlarına girilir, ilgili bağlantı seçilir. Ardından “Data Access Mode” ile verinin ne yöntemle alınacağı seçilir. Seçenekler “Table or view” veya “Sql Command” olabilir. Ayrıca bunlar değişkenden de alınabilir. Yine daha hızlı transfer için “Fast Load” seçenekleri de bulunur. “Table or view” seçildiğinde bir alttaki seçenekte veri alınacak tablo veya görünüm seçilir. SqlCommand da seçildi ise bir Sql sorgusu yazılacak bir ekran açılır.

“Table or view- Fast Load” seçildiğinde görüntülenen seçenekler şunlardır:

Keep Identity: Hedef tabloda identity kolonu varsa; ancak bu aktarımda identity ile değer üretilmesi istenmiyorsa, identity kolonuna kaynakdaki veri yazdırılmak isteniyorsa seçilmelidir.
Keep Nulls: Hedef tablodaki default değer tanımlanmış bir kolona veri aktarılırken kaynak kolonda veri null ise hedefe de null geçmesi için, hedef tablodaki default değerin kullanılmaması için seçilmelidir
Table Lock: Hedef tabloya veri aktarırken tabloyu işlem yapılmaması için kilitler
Check Constraints: Hedef tablodaki foreign key, check kısıtlarının kontrol edilmeyip, verinin kısıtlara uymasa da insert edilmesi için kullanılır.

Daha sonra Mappings ekranı seçilerek Kaynak ve Hedef kolonlar birbiriyle eşleştirilir. Eşlenen kolonların veri tipleri aynı olmalıdır.

EXECUTE SQL TASK

Bir veri kaynağında bir SQL cümlesinin çalıştırılmasını sağlar. Örneğin DW a her gün veri aktarırken, önce var olan verilerin temizlenmesi için bir “Execute SQL Task” kullanalım. Bunun için “Control Flow”a bir “Execute SQL Task” sürüklenir, çift tıklanarak özelliklerine girilir.

Öncelikle SQL cümlesinin hangi veri kaynağı üzerinde çalışacağı belirlenmelidir. Bunun için bir Connection Type ve Connection seçilir. SQLSourceType 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 seçenek değişecek ve Direct input için bir Sql cümlesi(Sql Statement) istenecektir; File Connection için Sql sorgusunun bulunduğu dosyanın bağlantısı, Variable için ise içinde çalıştırılacak sorgunun bulunduğu değişkenin adı istenecektir.

Örnek Proje

Projede DW deki tüm tablolar her gün silinip doldurulmayacaktır. Sadece günlük değişen veriler doldurulacaktır. Bunun için DW da bir çalıştırma logu tablosu oluşturalım.

create table DW_Calistirma_Log
(
Log_PK int identity not null primary key,
CalistirmaTarih date not null,
BaslangicTarihi date not null,
BitisTarihi date not null,
IslemYapildiMi bit not null
)

Bu log a her gün çalışan SSIS paketinin son adımında ertesi gün için CalistirmaTarihi, BaslangicTarihi ve BitisTarihi ertesi gün olmak üzere ve IslemYapildiMi 0 olacak şekilde kayıt atılacaktır. İlk gün çalışmak üzere kayıt yapalım.

insert DW_Calistirma_Log
values ('20150214', '20150214', '20150214', 0)

Projeye Baslangic ve Bitis adında 2 değişken tanımlayalım.

Projenin en başına bir “Execute SQL Task” ı ekleyerek orada aşağıdaki Sql cümlesini ekleyelim. Bu cümleyle her gün doldurulacak verinin başlangıç ve bitiş tarihleri döndürülecek ve Baslangic ve Bitis adındaki değişkenlere aktarılacaktır.

select convert(varchar, BaslangicTarihi, 112) BaslangicTarihi, 
convert(varchar, BitisTarihi , 112) BitisTarihi 
from DW_Calistirma_Log
where Tarih = cast(getdate() as date)
and IslemYapildiMi = 0

ResultSet kısmında “Single Row” seçtik; çünkü bu sorgudan tek satır dönmektedir. Bu satırda gelen BaslangicTarihi ve BitisTarihi verilerini Baslangic ve Bitis değişkenlerine aktarmak için “Parameter Mapping” sekmesini seçip atamaları yapalım.

Artık kaynaklardan DW ya veri aktaran “Data Flow Task” larda “Sql Command” seçilip burada GuncellemeTarihi iki parametre arasında olan veriler aktarılmalıdır.

Bunun için gerekli parametre mappingi yapılmalıdır.

Yine tüm adımlar çalıştıktan sonra ertesi günün kaydını yapan bir “Execute Sql Task” tanımlanmalıdır.

Buradaki Sql cümlesi şudur:

declare @BasTarih date, @BitTarih date
set @BasTarih = ?
set @BitTarih = ?

update DW_Calistirma_Log
set IslemYapildiMi = 1
where BaslangicTarihi = @BasTarih
and BitisTarihi = @BitTarih

declare @SonrakiBasTarih date, @SonrakiBitTarih date
set @SonrakiBasTarih = dateadd(day, 1, @BitTarih)
set @SonrakiBitTarih = dateadd(day, 1, getdate())
insert DW_Calistirma_Log
values (@SonrakiBitTarih, @SonrakiBasTarih, @SonrakiBitTarih, 0)

Sorgunun parametreleri de şu şekilde map edilmelidir:

5/5 - (1 vote)

Tarık Akarsu Muhammet Tarık Akarsu

Microsoft SQL Server Integration Services (SSIS)