SSIS ile Excelden Veri Aktarma ve Olası Sorunlar

6. December 2010

Excel'den veri aktarmak zorunda kalmayan bir kimse yoktur. Hatta ve hatta bunu ilk seferde problemsiz başarabilmiş hiç kimse de yoktur J Bu nedenle bu yazıda excel'den veri aktarırken karşılaşabileceğiniz bazı genel durumları ve nasıl hareket etmeniz gerektiğini anlatmaya çalışacağım. Öncelikle hepimizin hem fikir olması gereken bir nokta var. Excel müthiş bir araçtır ama veri saklama aracı olmamalıdır. Excel raporlama aracıdır aslında. Ancak ne zaman ki veri saklama aracı haline gelir, işte o zaman da bir sürü sorunu da beraberinde getirir. Entegrasyonlarınızda olabildiğince Excel verisi kullanmamanızı öneriyorum. Ben ne kadar önersem de yine de tutulacak bu yüzden bari tutarken ne gibi şeylere dikkat etmeniz gerektiğini anlatayım isterim.

Bir veri kaynağı olarak Excel:

  1. Excel gerçek bir veri tabanı/veri kaynağı değildir; öyle düşünülmemelidir.
  2. Excel içerisinde tuttuğu verilerin metadata bilgilerini barındırmaz.
  3. Barındırmadığı için de içerisinde bulunan verileri almak isterseniz veri tiplerini tahmin eder.
  4. Excel içerisinde aşağıdaki objeleri veri kaynağı olarak kullanabilirsiniz:
    1. Worksheet (Sonuna $ işareti eklenmiş bir biçimde: [Sheet1$]
    2. Worksheet içindeki Named Range (MyRange)
  5. Excel, registry'de bulunan JET Driver özelliği gereği ilk 8 satırı okur ve bu şekilde veri tiplerini tahmin eder.
  6. Eğer aynı kolonda karışık veri tipiyle karşılaşırsa (hem Text, hem numerik) numerik baskın çıkar ve text olan alanlar null olarak döner
  7. Bu işlemi engellemek için Registry'de IMEX ayarını kontrol etmek ve bağlantı cümlesinde IMEX=1 demek gereklidir. (Bkz: http://support.microsoft.com/kb/194124/en-us)
  8. Excel text kolonlarda data tiplerini belirlemek için yaptığı araştırma sırasında uzunlukları kontrol eder. Eğer 255 karakterden kısaysa örnek olarak baktığı satırlar, o kolonun tipini string 255 yapar. Ancak 255 yaptığı kolonda 255'ten daha uzun satırlar da olabilir o zaman satırdaki yazının 255 karakterden sonrası alınmaz, uçurulur. (Ayar için: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel à TypeGuessRows)
  9. Excel sadece aşağıdaki veri tiplerini destekler:
    1. Numeric – double-precision float (DT_R8) (Yani tüm nümerik değerler aslında float olur)
    2. Currency – currency (DT_CY)
    3. Boolean – Boolean (DT_BOOL)
    4. Date/time – datetime (DT_DATE)
    5. String – Unicode string, length 255 (DT_WSTR)
    6. Memo – Unicode text stream (DT_NTEXT)
  10. Excel'den veri çekerken iki adet driver kullanılır.
    1. JET Driver (Sadece Excel 2000 ve Excel 2003)
    2. ACEOLEDB Provider (Tüm Excel tipleri)
  11. Diğer tüm alışılmadık durumlarda ise Madde 1 geçerlidir.

 

Şimdi dilerseniz biraz detaya girelim.

 

Excel Sorguları

Excel'den veri çekerken iki şekilde sorgu yazabiliriz.

  1. Worksheet (Select * from Worksheet)
  2. Named Range (Select * from NamedRange)

Select * FROM [Sheet1$]

Select * from MyRange

 

SSIS içerisinden ise şöyle görünür:

 

Excel Driver Registry Ayarları:

 

Mixed Datatypes ve IMEX=1

SSIS paketi içerisinde Excel bağlantısını gerçekleştirdiğimizde yukarıda bahsetmiş olduğum Text ve Numerik alanların nasıl ele alınacağını belirten bir ayar yapabiliyoruz. Eğer karışık veritipi tutuluyorsa tüm veriyi string olarak almak için excel bağlantı cümlesine bir adet özellik ekliyoruz: IMEX=1

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Programming\NET Projects\My Developments\Personal Webs\blog\SSISVeExcel.xlsx;Extended Properties="EXCEL 12.0;HDR=YES; IMEX=1";

 

Veri Tipleri Tahminleme

Dediğimiz gibi Excel veri tiplerini tahmin ederken ilk 8 satırı okur. Bu nedenle ilk sekiz satır sonrası veri tipi değişirse problem olacaktır. Excel veri tiplerine karar verirken nasıl hareket ettiğini anlamak için dilerseniz aşağıdaki örneğe bakalım. Elimizde bir adet tamamiyle karışık veri bulunan bir excel var diyelim:

Peki bu kolonları SSIS Excel Source nasıl algılamış sizce?

İlginç? Bence de.

Şimdi dilerseniz ilk sekiz kolonu saçma sapan verilerle dolduralım, sonraki kolonları ise yukarıdaki gibi yapalım. Bakalım SSIS Excel Source ne yapacak?

 

Vee Sonuç:

Şimdi, sizi burda neler patlatır?

  1. TextCol: Patlatmaz
  2. Numeric Col: Patlatabilir
  3. DatetimeCol: Patladınız. (Bu arada elle yazdım "15 Mayıs 2010 Cumartesi" diye gerçek tarih girip Cell Format'tan yapmadım.)
  4. BooleanCol: Patladınız. (10.Satır)
  5. MemoCol: Patladınız (10.Satır)

 

Peki Veritabanına Atınca Ne Olur?

  1. Atamazsınız zaten çakılır, ahanda hata kodu:

Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "MemoCol" (51) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Error: 0xC020902A at Data Flow Task, Excel Source [1]: The "output column "MemoCol" (51)" failed because truncation occurred, and the truncation row disposition on "output column "MemoCol" (51)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

 

  1. Atmaya kalkışabilirsiniz, yine çakılır:

Error: 0xC020901C at Data Flow Task, OLE DB Destination [58]: There was an error with input column "DateTimeCol" (83) on input "OLE DB Destination Input" (71). The column status returned was: "The value could not be converted because of a potential loss of data.".

 

  1. Hadi inat ettiniz, attınız diyelim üstelik Excel'i değiştirdiniz.

O zaman da nur topu gibi NULL'larınız olur, Truncate edilmiş text alanlarınız olur, müthiş tarih değerleriniz olur:

 

Sonuç olarak , hep birlikte hatırlayalım; Madde 1:

  1. Excel gerçek bir veri tabanı/veri kaynağı değildir; öyle düşünülmemelidir.

Dataflow Components, Sources,Destinations , , ,

Comments (2) -

Sinem
Sinem
11/14/2011 3:45:31 PM #
İyi güzel de, bize verileri excelde gönderenlere söylemek lazım veri kaynağı değildir diye. Biz o verileri mecburen bir şekilde alacağız da, nasıl?
1/11/2012 3:22:47 AM #
Sinem selam,

Excel'in kullanılmasını engellemek mümkün değil tabii ki. Ancak bu noktada excel içerisinde veri veren kişilere bu durumun doğru olmadığını, entegrasyon için problem yaratabileceğini ve problem yarattığında gelip sana kızmamaları gerektiğini anlatabilirsin mesela.

Teşekkürler

Add comment




biuquote
  • Comment
  • Preview
Loading