Onlangs ben ik een conversie-uitdaging aangegaan waarbij 2.1 miljoen XML bestanden (relationele nawbestanden) geconverteerd/geimporteerd moesten worden naar een SQL server database in een afwijkend datamodel. Deze 2.1 miljoen XML bestanden gebruikten 4 verschillende XML formaten maar moesten wel allemaal ingelezen worden in het zelfde datamodel. Ik had de beschikking over een Windows server en SQL server 2005 om dit voor elkaar te krijgen.

Bij een dergelijke conversie loop je in ieder geval tegen de volgende uitdagingen aan:

  • Windows kan niet meer dan ongeveer 64000 bestanden per map aan.
  • Windows is niet gebouwd om snel met dit soort hoeveelheden bestanden om te kunnen gaan.
  • Er moet XSL transformatie worden toegepast. (4 verschillende transformaties)
  • De referentiele integriteit moet ‘buitenom’ bewaakt worden
  • De codering van de karakterset

Ik heb dit conversietraject opgelost met behulp van SQL Server Integration Services (SSIS) en zal globaal uitleggen hoe ik dit heb gedaan. Let op! Alleen voor techneuten.

De stappen

Stap 1: XML bestanden ordenen
Ik heb de 2.1 miljoen bestanden verdeeld over ca 30 mappen met ieder circa 64000 bestanden. Deze 30 mappen staan weer in een hoofdmap. Meer bestanden per map is niet mogelijk omdat windows NTFS hier niet mee kan omgaan. Deze XML bestanden worden straks verwerkt met behulp van SSIS.
Let op! Kies de harddisk die kortste toegangstijd heeft.

Het is heel vervelend om hierna met deze mappen te werken vanuit de windows verkenner omdat windows alle mappen die zich 1 niveau dieper in de boomstructuur bevinden dan de map waarin je werkt alvast ‘inleest’. Je begrijpt dat het enige tijd duurt voordat de verkenner dit proces heeft voltooid met 2.1 miljoen bestanden.

Stap 2: XSLT en XSD maken
Ik maak een XSLT en een XSD. De 2.1 miljoen XML bestanden zijn volgens 4 formaten opgesteld. De XSLT transformeert straks al deze berichten naar 1 uniform XML formaat dat ik later met 1 routine kan inlezen in de database i.p.v. 4 verschillende. De XSD beschrijft hetde opbouw het uniforme XML bericht. Deze beide bestanden heb ik straks nodig in SSIS.

Stap 3: Resultaatdatabase in SQL server aanmaken
Uit de XML bestanden moesten KvK dossiers (naw gegevens) en bijbehorende handelsnamen worden opgeslagen in 2 tabellen. Hiervoor heb ik een aparte database aangemaakt in SQL Server.
Let op! Zet deze resultaatdatabase op de snelste harddisk, echter het liefst een andere schijf dan waar de XML bronbestanden staan.

Stap 4: Project maken in SSIS
Ik heb een project aangemaakt in SSIS. Hierbij ziet de control flow er als volt uit:

control-flow

Hier volgt de uitleg van de drie objecten:

1) For Each Loop container.
Deze for each loop container pakt alle bestanden met de extentie .xml op die staan in een opgegeven map (inclusief submappen). Uiteraard is de hoofdmap uit stap 1 de map die als basis geldt.

collection

Ik definieer een ‘variable mapping’ (zie screenshot) zodat ik in de overige processtappen binnen de loopcontainer beschikking heb over de bestandsnaam van het het opgepakte XML bestand.

variable-mappings
2) XML task

Converteert het opgepakte XML bestand m.b.v. een XSLT (Stap 2), stopt het resultaat in een variabele en valideert dit resultaat met de in stap 2 gemaakte XSD.

general

Let op!
- De Operation Type moet ‘XSLT’ zijn en Destination Type ‘Variable’.
- De file connection ‘XSX’ is een ‘Connection Manager’ van het type ‘File’ waarvan de ‘Connection String’ parameter wijst naar het XSLT bestand.
- De fileconnection ‘Test’ is een ‘Connection Manager’van het type ‘File’ waarvan de ‘Connection String’ dynamisch is. Dit is te bewerkstelligen door een Property Expression voor deze connection manager waarbij de Property ‘ConnectionString’ de expression ‘@[User::FileName]‘ krijgt. Dit ziet er als volt uit:

property-expression

3) Data Flow Taks

Deze dataflow task schrijft de output van één geconverteerd bestand naar de database. In dit geval een KvK record in een tabel met algemene naw gegevens en nul-of-meerdere handelsnamen voor dit kvk dossier naar een tabel voor handelsnamen. Dit ziet er als volgt uit:

dataflowtask

Uiteraard is dit laatste voor een ieder verschillend echter er zijn 3 aspecten die altijd zullen overeenkomen:

  • Hou er rekening mee dat als het XML geconverteerde XML bestand geen foreign key naar het hoofdrecord heeft in de relationele records (in mijn geval de handelsnamen) dat je deze alsnog tovoegt. Anders ben je na de conversie niet meer in staat om de juiste relationele records te vinden bij het hoofdrecord. In mijn geval de juiste handelsnamen bij het kamer van koophandeldossier. Ik doe dat zelf met het ‘derived column’ element.
  • Om in de stap 3 aangemaakte database de resultaten weg te schrijven heb ik twee ‘OLE DB Destination’ elementen gebruikt (voor elk recordtype één). 
  • De datasource is een ‘XML source’ waarbij ‘Acces Mode’ de waarde 2 heeft. D.w.z. de input is niet een bestand maar een variabele. Uiteraard is het van belang om ‘XMLDataVariable’ te vullen met de juiste variable naam (zie hieronder).

xml-source

Tot slot

Het is natuurlijk onmogelijk om alle stappen in detail te bespreken. Ik heb geprobeerd de belangrijkste elementen te bespreken. Met deze oplossing was ik in staat om in circa 1 dag (24 uur) alle bestanden te converteren en in te lezen. Dit betekent dat er circa 25 bestanden per seconde konden worden geconverteerd.