Makale Özeti

DTS (Data Transformation Services) paketleri, belirli formatlardaki verileri, bir kerede veritabanına aktarmamızı kolaylaştıran SQL Server’ın güçlü özelliklerinden biridir. DTS paketleri kullanılarak çeşitli veri kaynaklarında bulunan bilgiler üzerlerinde işlemler yapılarak veritabanındaki tablolara istenilen şekilde kaydedilebilir. DTS paketinin oluşturulması ve web uygulaması aracılığıyla çalıştırılması ile ilgili bilgileri iki makale ile vermeye çalışacağım. İlk makalede DTS paketinin oluşturulmasını ikinci makalede ASP .NET aracılığıyla çalıştırılmasını inceleyeceğim. İkinci makaleye linkler bölümünden ulaşabilirsiniz.

Makale

Senaryo:

Bir şirketin kullanıcılardan verileri belirli bir formatta aldığını ve bu verileri toplu halde veritabanına aktarmak istediğini düşünelim. Dosya formatı olarak Excel’i kullanalım. Senaryoya göre kullanıcılar bir web uygulaması aracılığıyla Excel formatındaki dosyaları sunucuya yüklüyorlar. Sunucuya gelen dosyadaki veriler SQL Server’daki DTS paketi çalıştırılarak veritabanındaki ilgili tablolara yerleştiriliyor.

SQL Server’daki tablolarının yapısını aşağıdaki gibi tasarlayalım:

Kullanıcının yüklediği Excel dosyası tblUrunStok tablosuna girilecek verileri içerir. Excel dosyasındaki alanları aşağıdaki gibi tasarlayalım:

Barkod No

Stok Sayısı

Sipariş Sayısı

Sipariş Birim Fiyati

Açıklama

9771303757908

10

55

1.500

 

0043594839712

150

600

300

 

Dosyanın yüklendiği ekranda kullanıcıdan bu bilgiler için dönem seçmesi ve bir açıklama yazması bekleniyor. Ayrıca kullanıcı bilgileri de uygulama tarafından logon olunan hesaptan alınarak tblGiris’teki Calisan alanına kaydedilir. Bu bilgiler DTS paketine değişken olarak gönderilecektir. Web formunun görünümü aşağıdaki gibidir:

Kullanıcının gönderdiği her Excel Dosyası için DTS paketi tblGiris tablosunda bir kayıt oluşturacak. Bu kayıt DTS paketine gönderilen Aciklama, Donem ve Calisan bilgilerini içerecek. Bunun ardından veri transferi işlemi yapılacak. Excel dosyasındaki tüm veriler tblUrunStok tablosuna yerleştirilecek. Veri transferinin ardından tblUrunStok tablosuna bu verilerin hangi girişe ait olduğunu ekleyebilmek için tblGiris tablosuna o transaction içinde eklenmiş son GirisID alınarak tblurunStok tablosunda GirisID değeri null olan alanlara yazılacak. Önce bu işlemi yapacak DTS paketini oluşturalım.

DTS Paketinin Oluşturulması:

Enterprise Manager’da Data Transformation Services klasörü altındaki Local Packages üzerinde sağ tıklayarak açılan menüden New Package’ı seçin.

Önce veri transferi için gerekli bağlantılar oluşturulmalıdır. Excel bağlantısını oluşturmak için sol taraftaki Connections bölümünden Excel bağlantı nesnesini seçin. Bu nesneye verilerin çekileceği dosyanın yerini göstermemiz gerekiyor. Senaryoya göre Excel dosyası kullanıcı tarafından sunucudaki belirli bir klasöre yüklenecekti. Excel bağlantısını bu klasör içindeki dosyayı alacak şekilde ayarlamamız gerekiyor. Örneğin ben Excel dosyasını c:\klasor altına yükleyeceğim. Dosya adı olarak urunStokBildirimi.xls’i kullanacağım. Buna göre bağlantıyı şu şekilde oluşturuyorum:

Daha sonra bu verilerin ekleneceği veritabanı için bağlantı oluşturmamız gerekiyor. Veritabanı SQL Server’da olduğundan Microsoft OLE DB Provider for SQL Server bağlantısını seçiyor um. Kullanıcı haklarını ve verileri yükleyeceği veritabanını belirliyorum.

Bağlantılar tanımlandıktan sonra Excel dosyasından veritabanına aktarımı yapacak nesnelerin hazırlanması gerekiyor. DTS paketine web sayfasından bazı bilgileri göndereceğimden bahsetmiştim. Bu bilgileri gönderebilmek için DTS paketi içinde Global Variable tanımlamam gerekiyor. Web sayfasından aldığım bilgileri bu Global Variable’ları aracılığıyla DTS paketine göndereceğim.

Global Variable oluşturmak için DTS paketi içinde sağ tıklayarajk açılan menüden Properties seçilir.

           

Açılan pencerede Global Variables sekmesine ilerledikten sonra değişken tanımlamalarını aşağıdaki gibi yapabiliriz:

DTS paketi için değişkenlerin oluşturulmasının ardından veri transferi için gerekli nesneleri hazırlamalıyız. Bunun için paketlerde en çok kullanılan iki Task’ı kullanacağız: Execute SQL Task ve Data Driven Query Task.

*     Execute SQL Task:

Execute SQL Task nesnesi DTS paketi içinde SQL gerekli adımlarda SQL sorgusu çalıştırmak için kullanılır. Bu nesneyi veritabanında excel dosyasını temsil edecek bir giriş kaydı oluşturmak için kullanacağız. Excel dosyası ile gelen bilgileri hangi çalışanın hangi dönem için yolladığını ve varsa açıklama yazısını bu nesneyi kullanarak tblGiris tablosuna ekleyeceğiz. Bunun için önce DTS paketine Task bölümünden Execute SQL Task nesnesini sürükleyin. Açılan pencerede Description bölümüne Giris Bilgileri Ekle yazın. Existing Connection bölümünde SQL Server için oluşturmuş olduğumuz bağlantıyı seçin. SQL Statement bölümüne şu kodu yazın:

insert into tblGiris (calisan,donem,aciklama) values (?,?,?)

tblGiriş tablosuna eklyeceğimiz veriler DTS paketi için tanımladığımız Global Variable’lardan gelecek. Bunun için SQL sorgusunda ? (soru işareti) kulladık. Soru şareti burada bizim parametrelerimizi temsil ediyor. Şimdi bu parametreleri değerlerini nerede alacaklarını söyleyebilmek için Parameters... butonuna tıklayalım. Açılan pencerede sırasıyla hangi parametrenin hangi global Variable’dan değer alacağını belirleyelim.

Böylece Execute SQL Task nesnesini hazırlamış olduk.

*      Data Driven Query Task:

Data Driven Query Task nesnesi veri transferi sırasında özel işlemler yapan kodlar çalıştırmamıza izin verir. Biz Excel dosyasından verileri bu nesneyi kullanarak SQL tablosuna aktaracağız. Bu arada tabloya eklenen her satıra bu verinin hangi giriş dosyasından geldiğini bildiren GirisID değerini de ekleyeceğiz. Bunun için önce DTS paketine Task bölümünden Data Driven Query Task nesnesini sürükleyin. Açılan pencerede Description bölümüne Bilgileri Aktar yazın. Daha sonra Source sekmesinde olduğumuzdan kaynak olarak Microsoft Excel 97-2000’i ve Table/View bölümünde UrunStok’u seçin. Böylece verilerin kaynağını belirtmiş olduk.

Bindings sekmesinde de verilerin nereye aktarılacağını seçeceğiz. Tahmin edebileceğiniz gibi Connection olarak SQL Server ve tablo olarak tblUrunStok’u seçeceğiz.

           

Transformations bölümüne Excel’deki hangi alanın tblUrunStok’taki hangi alana aktarılacağını belirteceğiz. Bunun için eğer varsa bütün trasnformation’ları silin daha sonra önce soldaki listeden bir alan seçtikten sonra sadaki listeden ilgili alanı seçin. New butonuna tıklayarak bir Transformation oluşturun.

           

Transformation tipi olarak ActiveX Script’i seçin. Açılan pencerede Properties butonuna tıklayarak ActiveX Script’in oluşturulduğundan emin olun. Eğer kod resimdeki gibi oluşturulmamışsa Auto Gen.’e tıklayarak oluşturulmasını sağlayın.

Bu işlemi GirisID ve tablonun otomatik artan alanı UrunBilgiID dışındaki 5 alan için tekrarlayın. Dilerseniz tek Transformation içinde de bu işlemi yapabilirsiniz.

Alanları bağladıktan sonra geriye veri girişi için kullanılacak kodu yazmmak kalıyor. Bunun için Queries sekmesine ilerleyin ve Query Type’tan Insert’ü seçtikten sonra sorgu bölümüne şu kodu yazın:

insert into tblUrunstok values (ident_current('tblGiris'),?,?,?,?,?)

ident_current('tblGiris') ile tblUrunStok tablosuna bu bağlantıda eklenen her veriye yine bu bağlantıda eklenmiş olan GirisID’yi vermiş oluyoruz. Böylece tblUrunStok’taki verilerin hangi giriş ile ilişkili olduğunu tutabileceğiz. Bu koddaki parametreler Excel dosyasından çektiğimiz alanlardan gelecek. Bunları ayarlamak için Parse/Show Parameters butonuna tıkladıktan sonra parametrelere doğru sırayla doğru alanları atamalıyız.

Bu işlemle birlikte Data Driven Query Task nesnesini de hazırlamış bulunuyoruz. Şimdi bu iiki nesnenin çalışma sırasını belirlemeliyiz. Önce Execute SQL Task’ı ve Data Driven Query Task’ı seçtikten sonra Workflow menüsünden On Success’i seçin. Böylece nesneler arasında bir akış oluştuğunu göreceksiniz.

DTS paketi ve Transaction

DTS paketinde son yapmamız gereken şey herhangi bir adımda bir hata oluştuğunda önceki adımların geri alınmasını sağlayacak bir özelliği ayarlamak olacak. Önce DTS paketi üzerinde sağ tıklayıp çıkan menüde Package Properties’i seçin. Daha sonra Advanced sekmesine ilerleyip Transactions bölümünü resimdeki gibi ayarlayın:

Böylece DTS paketinin çalışırken transaction kullanmasını sağladık. Şimdi oluşturmuş olduğumuz adımların bu transactionı kullanabilmesini sağlamamız gerekiyor. Bunun için her birinin üzerinde sağ tıklayıp açılan menüden Workflowà WorkFlow Properties’i seçmelisiniz. Açılan pencerede Options sekmesine ilerledikten sonra Transaction bölümünü aşağıdaki gibi ayarlamalısınız. Bunu her iki Task için de ayarlamanız gerekiyor.

Böylece Task’lardan birinde bir hata olduğunda DTS paketi o bağlantı içinde yapmış olduğu tüm işlemleri geri alabilecek.  Transactionların çalışabilmesi için Windows servislerinden DTC'nin (Distributed Transaction Coordinator) çalışıyor olması gerekiyor. 

Bu adımla birlikte DTS paketi oluşturma işlemi sona eriyor. Paketi DTSUrunStok adıyla SQL Server’a kaydedin ve üzerinde tıklayıp Execute Package'ı seçerek doğru çalıştığından emin olun.

Network üzerinde DTS paketini çalıştırmak

Eğer DTS paketinin bulunduğu SQL Server ile web uygulaması ayrı bilgisayarlardaysa Transaction'ların çalıştırılabilmesi için her iki bilgisayarın MSDTC servislerinde Network Access seçeneği işaretlenmiş olmalıdır. Bunu yapabilmek için BaşlatàÇalıştır'ı seçtikten sonra dcomcnfg.exe'yi çalıştırın.  Açılan pencerede ComponentServicesàComputersàMyComputer üzerinde sağ tıklayın ve Properties'i seçin.  MSDTC sekmesine gelin ve Security Configuration'ı seçin.  Açılan pencerde Network Access'i aktif hale getirin.  MSDTC servislerinin doğru çalışıp çalışmadığını anlamak için dtcping.exe'yi kullanabilirsiniz.

Bu makalede bir DTS paketinin oluşturulması ve Transaction içinde çalıştırılması konularını inceledik.  Bu DTS paketinin bir web uygulaması aracılığıyla çalıştırılması konusu için devam makalesini inceleyebilirsiniz. DTS paketi ve Excel dosyasını Download bölümünden indirebilirsiniz.

DTS Paketi Oluşturmak ve ASP.NET ile Çalıştırmak - 2

DTS paketi

Excel Dosyası