Registry-ben könyékig, avagy Excel betöltések buktatói

Itt vannak ezek az Excel fájlok, amelyeket be kell tölteni az adattárházba, és amelyektől egy adattárház fejlesztő vagy üzemeltető folyamatosan retteg. Vajon jól töltötte ki az üzleti terület őket? A számok helyére tényleg számokat írtak? Véletlenül nem szúrtak be üres sorokat a tábla közepére? És még sorolhatnám, de ezekről már olvashatott az Excel fájlok betöltésének buktatói című bejegyzésben.

Tényleg röhejes, de ezek az apró Excel fájlocskák azok, amelyek legjobban veszélyeztetik a betöltés sikerességét. Persze kikerülhetjük őket egy nagy projekten, ahol a projekt elbírja egy olyan alkalmazás kifejlesztésének költségét, ami lehetővé tesz az üzleti felhasználóknak, hogy a forrásrendszerrel nem rendelkező adatokat ne Excelbe, hanem az erre a célra kifejlesztett alkalmazásba rögzítsék, de egy kis BI projekt ezt nem bírja el. Marad az Excel.

Nem elég hogy attól kell rettegnünk, hogy az üzleti felhasználó nehogy elírjon valamit az Excelben, itt van még ez a Jet OLE DB provider aki szintén rátesz egy lapáttal. A Jet OLE DB provider ugyanis megpróbálja kitalálni helyettünk az Excel táblák oszlopainak adattípusait. Ez néha azonban nem sikerül neki, hiszen az eljárás úgy működik, hogy kiolvassa az adott oszlop első 8, azaz nyolc sorát és abból eldönti a teljes oszlop adattípusát. Ha az első nyolc sor szöveget tartalmaz, és azok egyike sem haladja meg a 256 karakternyi hosszat, akkor beállítja azt 255 hosszú unicode stringre

Ekkor jövünk mi adattárház fejlesztők, akik be akarják olvasni az adatokat az Excelből és létrehozunk egy új Integration Services adatbetöltő csomagot. A betöltő első feladata az lesz, hogy felolvassa az Excelből a sorokat és bepumpálja őket a memóriába, majd kitegye őket egy adatbázisba. El is kezdi böcsülettel a munkáját. Ez első 8 sorral megbirkózik, hiszen azok tartalmát analizálta. De mi történik ha a 9. sor 255 karakternél hosszabb? Dob egy hátast és leáll az adattárház betöltése. (Megjegyzem teljesen jogosan, hiszen az első 8 sor alapján csak 255 karakterhosznyi területeket foglalt le neki a memóriában, ahová a 256 hosszú szöveg már nem fér el)

Advanced editor

Jól felkészült adattárház fejlesztők ilyenkor rohannak az advanced fülre és kézzel átírják a memóriába belépő adatok típusát egy nagyobb adattípusra. De nem megy. Nem megy, mert használjuk az SSIS beépített hibás rekord átirányítóját, (OLE DB Destination Error Output) aminek adattípusát nem tudjuk megváltoztatni. (A hibaág is pont úgy viselkedik, mint a betöltés során használt memória, tehát az is 255 hosszú, és oda se tudja kipumpálni a 256 hosszú mezőt

Nem kell hibalista

Persze megszüntethetjük a hibakezelést és mondhatjuk, hogy ne irányítsuk át a hibás sorokat egy hibaállományba (és ekkor meg is oldódna a probléma), de akkor szegény adattárház üzemeltetőnek kéne kitalálni, hogy melyik sor nem ment be az adattárházba. Na ekkor írnánk alá frissen épített adattárházunk halálos ítéletét.

Verjük át a Jet OLE DB provider-t!

Hibakezelés tehát kell. Mi mást csinálhatnánk? Verjük át a Jet OLE DB provider-t! Az első betöltéskor töltsük fel az oszlopokat 255-nél hosszabb szövegekkel és írjuk meg erre a fájlra a betöltőt. Mit csinál a Jet OLE DB provider? Analizálja a kialakult helyzetet, és rájön, hogy kevés lesz neki a 255 hosszú hely a memóriában. Hopsz. Át is írja rögvest NText-re, amibe már minden belefér.

Bár ez a megoldás elsőre elég balkáninak tűnik (és lesz is egy jobb), de sajnos csak ez az üdvözítő út létezik arra, hogy explicit határozzuk meg azon adattárház töltő SSIS csomagok adattípusát, amelyek használják a hibás sorok átirányítását hibalistára. Ha nem tudjuk előre, hogy az Excelbe beírt adatok meg fogják-e haladni a 255-ös hosszt, de gyanítjuk, hogy igen, akkor bizony ezt kell tennünk. Ha úgy gondoljuk, hogy az Excelekben tárolt adatok hosszának maximuma nem fog eltérni az Excelben aktuálisan tárolt adatok hosszának maximumától, akkor van más megoldás is.

Túrjunk bele a registry-be

Bár a registry matatástól mindenki fél, azt kell hogy mondjam, hogy ez a hivatalosan támogatott útja annak, hogy a Jet OLE DB provider működését megváltoztassuk. A registry-ben ugyanis megadhatjuk azt, hogy mekkora legyen az a minta, amit a Jet OLE DB provider letapogat az adattípus meghatározásához. Ez az érték alapértelmezettként 8 sor. Ezt felülírhatjuk 0-16 ig, ahol az 1-től 16-ig terjedő számok határozzák meg a letapogatandó sorok számát, tehát a 16 az 16 sort jelent. A nulla pedig azt jelenti, hogy a Jet OLE DB provider az adattípus meghatározásához kb. 16000 sort fog beolvasni. (Nem tudom a pontos számot, mert nem találom meg az interneten, de nagyságrendileg ennyire emlékszem)

A minta nagyságát, amit a Jet OLE DB provider vizsgál az adattípus meghatározásához az itt leírt módon változtathatjuk meg a registry-ben

Összefoglalva:

Mivel az Excel fájlokban tárolt információknak nincs rögzített adattípusa és hossza, mint mondjuk egy táblának, ezért a Jet OLE DB provider megpróbálja kitalálni azt. Mintát vesz az oszlop első 8 sorából és az ezek alapján kikövetkeztetett adattípust adja vissza az Integration Services-nek. Az Integration Services hibaága ezt az adattípust kőbevésettnek tekinti, és ennek megfelelően készíti fel saját memória területeit az adatok befogadására. Ha a Jet provider rosszul határozta meg az adattípust, akkor a betöltés le fog halni.

Hogy ezt elkerüljük, két dolgot tehetünk:

1. Megnöveljük a minta méretét, amit a Jet provider letapogat, és így megnöveljük a valószínűségét a helyes adattípus eltalálásának
2. Gondoskodunk róla, hogy a minta tartalmazzon egy olyan adatot, amely reprezentálja a teljes oszlopot, így biztosra mehetünk abban, hogy a Jet provider helyesen határozza meg az adattípust

Kapcsolódó anyagok:

• Miért importál NULL sorokat a DTS az Excelből?
• Excel fájlok betöltésének buktatói - és hogyan kerülje el őket?