Microsoft SQL Server 2005 Integration Services

 

Auteur: Dr. Nico Jacobs (nico@u2u.be)

 

Microsoft vaart al enige tijd onder de vlag “innovatie door integratie”. De nieuwste editie van hun ETL (Extract, Transform, Load) tool zet dit extra in de verf door de naam Integration Services te dragen. In dit artikel nemen we deze opvolger van Sql Server Data Transformation Services (DTS) onder de loupe.

ETL: goochelen met data

De I in I(C)T blijft een belangrijk gegeven: hoe je het ook draait of keert, het basisgegeven van de informatica blijft het genereren, transporteren en stockeren van gegevens of data. In heel wat bedrijven zijn gegevens in verschilende formaten gestockeerd. Vaak moeten deze gegevens omgevormd en gecombineerd worden om in een bepaalde toepassing nuttig te zijn. Een typisch voorbeeld van dit laatste zijn analyse en rapporterings-toepassingen. Het is dan ook in deze context dat ETL-tools erg populair zijn om gegevens van allerlei bronnen op te vragen (Extraheren), om te vormen (Transformeer) en te stockeren (Laden). Er zijn voorbeelden van het gebruik van dergelijke tools legio: het combineren van bedrijfseigen met bedrijfsvreemde gegevensbanken, het op elkaar afstemmen van gegevensbanken na een samenvloeing van twee bedrijven, het overpompen van gegevens van een Acces naar een Oracle gegevensbank,... .

 

Microsoft bundelt zijn ETL-tool samen met de gegevensbank zelf. Voor SQL Server 2000 was dit Data Transformation Services, maar net zoals de andere onderdelen van dit pakket, is ook het de ETL tool danig herwerkt in de 2005 editie. Zodanig zelfs dat Microsoft er een nieuwe naam op gekleefd heeft: Microsoft SQL Server 2005 Integration Services (SSIS). Integratie wordt mogelijk door data van allerlei gegevensbronnen te lezen, en vervolgens om te vormen alvorens ze weer te gaan stockeren.

Data bronnen

Integration Services blijft nog steeds het uitgangspunt van Data Transformation Services trouw: met dit product kunt u data van eender welke gegevensbron die OLE DB ondersteunt als bron of eindbestemming van uw data-conversie gebruiken; SQL Server hoeft dus niet eens betrokken te zijn hierin. Als we kijken naar de lijst van mogelijke gegevensbronnen (zie Figuur 1), dan zien we hierin al enkele nieuwigheden ten opzichte van Data Transformation Services. Zo heeft Microsoft ondersteuning voor XML bestanden toegevoegd: uw gegevens kunnen uit een XML bestand gelezen worden. Een ander belangrijke nieuwigheid, die vooral bij de ontwikkelaars graag gezien zal zijn, is dat nu ook een DataReader als gegevensbron kan gebruikt worden.

Figuur 1: Naast OLE DB en tekst, aanvaard Integration Services nu ook invoer uit XML en zelfs rechtstreeks uit een DataReader.

Visual Studio Solution

Wat ook nog steeds hetzelfde is als in Data Transformation Services, is dat er twee interactieve manieren zijn om een ‘package’ of pakket (een verzameling ETL processen) te ontwerpen: ofwel via een Import/Export wizard, ofwel via een ‘designer’. Deze laatste is echter grondig gerestyled ten opzichte van Data Transformation Services. Om te beginnen is deze designer geïntegreerd in de Business Intelligence Studio, wat betekent dat het in Visual Studio ontworpen wordt (zie Figuur 2). Een ander verschil is dat een pakket als XML opgeslagen wordt, wat betekent dat we onder andere gebruik kunnen maken van Microsoft Visual SourceSafe om pakketten onder versie-controle te plaatsen. Dit betekent eveneens dat we pakketten programatorisch kunnen genereren of wijzigen (met bijvoorbeeld XSLT) mocht dit gewenst zijn. Een ander belangrijk nieuwigheid is de scheiding tussen de control flow en de data flow: waar in Data Transformation Services deze beide elementen in een complex schema vervlochten zaten, heeft elk nu een apart venster, wat complexe packages meteen overzichtelijker maakt.

 

Figuur 2: Een package maakt deel uit van een Solution, en wordt in Visual Studio ontwikkeld.

Control Flow

 

Figuur 3 toont de grafische weergave van de control flow van een pakket. De atomaire bouwstenen van zo’n flow zijn de taken. Voorbeelden van dergelijke taken zijn het versturen van email, het uitvoeren van een console-bevel of het opstarten van een ander pakket. Maar Integration Services bevat een aanzienlijk aantal taken die in Data Transformation Services niet aanwezig waren (zie Figuur 4 voor een lijst met alle control flow taken). Zo is er nu een XML taak, die toelaat XML documenten te valideren, transformeren, samenvoegen, DiffGrams te berekenen, Xpath queries op uit te voeren en XML documenten te ‘patchen’. Een andere nieuwkomer is de Web Service taak, die toelaat een webservice op te roepen. De uitvoer van zo’n Web service oproep kan zowel naar bestand geschreven worden als in een variabele gestockeerd worden om verder in het  pakket gebruikt te worden. Twee andere nieuwe taken zijn de WMI Data Reader en de WMI Event Watcher taak, die de integratie tussen database georiënteerde taken en besturingssysteem georiënteerde taken vergemakkelijkt.

Figuur 3: Deze control flow start eerst twee processen op om de condities voor een lus te bepalen. Vervolgens wordt de lus opgestart, die in elke iteratie een gegevensbank aanmaakt. Als de lus uiteindelijk afgelopen is, wordt een Data Flow taak gestart, tenzij ze faalt, want dan wordt er een email verzonden.

 

Figuur 4: de beschikbare control flow taken

Containers

Opvallende nieuwigheden in deze control flow zijn ook de containers. Deze bouwblokken laten toe om complexe, geneste operaties eenvoudig op te bouwen. Er zijn drie soorten containers: Sequence containers, For Loop containers en ForEach Loop containers. Een sequence container is gewoon een verzameling van andere taken: het zorgt voor een scope waarbinnen deze andere taken uitvoeren (zie bijvoorbeeld de Create databases container in Figuur 3). Wanneer de container geactiveerd wordt, activeert deze op zijn beurt alle taken binnenin  de container. Dit heeft een aantal voordelen. Zo kunnen bijvoorbeeld variabelen gedefinieerd worden die zo’n container als scope hebben. Een container kan ook in de toestand ‘disabled’ geplaatst worden, waardoor meteen alle taken binnenin deze container op non-actief geplaatst worden. Ook kan in de grafische voorstelling een container ingeklapt worden, waardoor een diagramma overzichtelijker wordt.

 

Beide iteratie containers zijn het equivalent van de gelijknamige lussen in VB.NET. Bij een For Loop container wordt een variabele aangepast totdat een bepaalde stopconditie bereikt is. Voor elke iteratie worden alle taken die zich in de container bevinden uitgevoerd, alsof ze een apart package zijn. Doordat, net zoals in Data Transformation Services, SQL Server Integration Services variabelen heeft, kunnen deze gebruikt worden binnenin de Loop om het gedrag van de taken daarin aan te passen. Een klein voorbeeldje. Stel dat we een aantal tabellen moeten aanmaken, eentje voor elk burgerlijk jaar, maar we kunnen het beginjaar en eindjaar niet hard coderen in ons pakket, deze gegevens worden uit de gegevensbank zelf ingelezen. In zo’n scenario zouden we eerst een “Execute SQL Task” gebruiken om uit de database het begin en eindjaar in te lezen, en vervolgens een For Loop container een variabele laten lopen van het beginjaar tot aan het eindjaar. In de container plaatsen we een Execute SQL statement dat, gebruik makend van de variabele, de juiste tabellen aanmaakt (zie Figuur 3).

 

Een For Each Loop itereert over een verzameling, zoals bijvoorbeeld alle bestanden in een folder. Dit kan handig zijn als we bijvoorbeeld alle logbestanden in een bepaalde directory in de database moeten laden, waarbij we niet op voorhand hoeven te weten hoeveel bestanden er precies zullen zijn.

Data Flow

De eigenlijke data transformatietaken worden op de data flow tab gedefinieerd. Waar we voor complexe opdrachten vaak aangewezen waren op het schrijven van een ActiveX script of een complex SQL statement, worden nu vele data transformaties eenvoudig mogelijk door de vele bouwstenen die de designer ons aanreikt. Zo zijn er nu data transformatie taken zoals aggregaties berekenen, sorteren, lookup doen enzovoort. Naast deze taken zijn er ook enkele ‘intelligente’ taken bijgekomen. Een voorbeeld van deze laatste is de Fuzzy Lookup. Deze gaat na of een record bij benadering voorkomt in een referentie-tabel, in tegenstelling tot de klassieke lookup, die enkel een exacte match toelaat. Zo kunt u op een automatische manier licht vervuilde data (typfouten, afkortingen,...) opkuisen. Figuur 5 toont het eindresultaat van zo’n Fuzzy Lookup, waarbij naast de kolom met ingelezen namen, een kolom toegevoegd is met de naam die volgens de Fuzzy Lookup de correcte referentie-naam is. Naast deze naam vindt u ook statistieken die weergeven hoe groot de overeenkomst is tussen de referentienaam en de ingelezen naam. Merk op dat, hoewel de techniek die hierachter schuil gaat gelijkenissen vertoont met technieken die gebruikt worden om spellingscorrectie in tekstverwerkers te doen (het opzoeken van woorden in een referentielijst), de aanpak toch danig verschillend is: bij de fuzzy lookup: Niet alleen kan (en moet) u bij de Fuzzy Lookup zelf uw referentielijst aanbieden, de aanpak is ook een taal-neutrale techniek: het systeem gaat er niet van uit dat de ingelezen termen tot een welbepaalde taal behoren.

 

Een andere taak die bij dergelijke taken kan helpen is de Fuzzy Grouping, waarbij ook zonder een referentietabel gelijkenissen tussen ‘vervuilde’ data kan opgespoord worden. Bij deze aanpak wordt een soort clustering techniek gebruikt, waardoor gekeken wordt hoe ‘ver’ elk woord van andere ‘vervuilde’ woorden verwijderd is.

 

ApproximateName	CorrectName	Similarity
Identity Confoozion Device	Identity Confusion Device	88%
Global Navigashunal System	Global Navigational System	91%
Multi-Purpose Rubber Ban'	Multi-Purpose Rubber Band	91%
Nonsplosive Cigar	Nonexplosive Cigar	92%
Tellykinesis Spoon	Telekinesis Spoon	92%
Cloakin' Device	Cloaking Device	92%
Th' Incredible Versatile Paperclip	The Incredible Versatile Paperclip	92%
Contack Lenses	Contact Lenses	93%
Fake Moestache Translato'	Fake Moustache Translator	93%
Effeckive Flashlight	Effective Flashlight	95%
Pocket Protecko' Rocket Pack	Pocket Protector Rocket Pack	95%
Unyversal Repair System	Universal Repair System	96%
Ultra Violet Attack Defenner	Ultra Violet Attack Defender	97%

Figuur 5: Eindresultaat van een Fuzzy Lookup

 

Wat ook handig is in de data flow is dat elke taak waarbij er records kunnen zijn die falen, we deze records heel eenvoudig kunnen omleiden naar eender welke andere component. Uit een component ontspringen immers twee pijlen: een groene, waarover de correct verwerkte data stroomt, en een rode, waarover de gefaalde data stroomt. Zo kunnen we in het voorbeeld van de fuzzy lookup bijvoorbeeld eerst een gewone lookup doen, en enkel de records voor wie de gewone lookup faalt doorsturen naar de (meer reken-intensieve) fuzzy lookup.

Gemakkelijker ontwerpen, testen, debuggen en deployen

Niet alleen heeft Microsoft aan de aangeboden functionaliteit gesleuteld, ook het gemak waarmee packages ontwikkeld en getest kunnen worden is sterk verbeterd. Zo wordt tijdens de uitvoering van een pakket binnen de designer grafisch weergegeven welke taken al opgestart zijn, welke succesvol afgerond zijn en welke gefaald hebben. Het is ook mogelijk om op elke connectie in een data flow een data viewer te plaatsen. Deze toont dan tijdens de uitvoering de data die over deze connectie stroomt. Dit is op vele manieren mogelijk: ofwel de gedetailleerde data in een grid bekijken, of bijvoorbeeld een grafische weergave in de vorm van een histogram. Figuur 6 toont een data flow met enkele data viewers in actie.

Figuur 6: Een data flow in uitvoering: De eerste stap is beëindigd (groen), de twee volgende stappen zijn in uitvoering (geel). Rechts tonen twee Data Viewers de gegevens die van de eerste naar de tweede taak stromen, zowel als histogram als in grid formaat; dit laat toe snel onregelmatigheden in de data op te sporen. Deze Data Viewers fungeren op de data flow tevens als breakpoint.

 

Een ander hulpmiddel bestaat uit breakpoints: op elke taak, zowel als op elke lijn code die in een script van het pakket staat, kan een breakpoint gezet worden. Op die manier kan de uitvoering tijdelijk stopgezet worden, en kan de toestand van het pakket geïnspecteerd worden, vergelijkbaar zoals dit met gewone .NET applicaties gebeurd. Tot slot is het ook mogelijk om logging en event handling te doen: SQL Server Integration Services bevat een verzameling van events die zich kunnen voordoen, gaande van een ‘Start Processing Task’ event tot een ‘Could not open Connection event’. Elk pakket kan een aantal loggers bevatten. Zo zijn er textfile loggers, xml loggers, database loggers en windows event log loggers. Elk van deze types loggers kan geconfigureerd worden om bepaalde events in zijn log weg te schrijven. Deze laatste manier van werken is vooral interessant om na afloop van de uitvoering te kunnen analyseren wat er precies gebeurd is. Willen we tijdens de uitvoering echter op een bepaald event reageren, dan kunnen we een event handler toevoegen. Zo’n handler is niet meer of niet minder dan een control flow, wat betekent dat we in deze handler dus de beschikking hebben over alle taken (inclusief data flow taken) die Integration Services aan boord heeft. En zelfs op de Event handlers kunnen event handlers gedefinieerd worden (zie Figuur 7).

Figuur 7: Event handlers kunnen op elke component van een Integration Services pakket geplaatst worden, zelfs op andere event handlers.

 

Integration Services Helpt ook in de deployment van een project. Zo heeft men, naast de klassieke mogelijkheid om deployment vanuit Visual Studio rechtstreeks te doen, ook de mogelijkheid om een deployment utility te bouwen bij het builden van een project (dit kan je aanvinken bij de properties van het project, zie Figuur 8). Er wordt dan automatisch een kleine applicatie aangemaakt die naar een andere server gekopieerd kan worden en daar de deployment doet.

 

Figuur 8: In de project Properties kan het aanmaken van een deployment utility aangevraagd worden.

 

Een ander deployment probleem zijn site-afhankelijke instellingen. Stel dat je een Integration Services pakket wil lopen op meerdere servers, maar elke server heeft eigen eigenschappen, zoals conncetion strings, timeouts, smtp servers,... We willen natuurlijk niet op elke server onze pakketten gaan editeren. De oplossing die Integration Services voor dit biedt vormt de Package Configuration. Hiermee kunnen we bij het aanmaken van een pakket aangeven dat bepaalde instellingen van het pakket niet hard-gecodeerd in het pakket zitten, maar uit een configuratiebestand komen. Dit biedt ons een waaier van mogelijkheden, zoals de configuratie-gegevens opslagen in een XML bestand, de registry, de SQL Server database of Windows omgevingsvariabelen. En zelfs de preciese locatie van deze configuratie-gegevens kan op zijn beurt weer uit pakket-variabelen gelezen worden, Wat bijvoorbeeld toelaat op elke server een tabel te plaatsen die als string de locatie van het configuratiebestand bevat. Figuur 9 toont een eerste stap in de configuratie creatie wizard.

 

Figuur 9: Met package configurations kunnen we zowat elk aspect van een pakket uit configuratiebestanden uitlezen, en bij gevolg site afhankelijk bepalen.

Besluit

SQL Server Integration Services kan met recht en rede de grote broer van Data Transformation Services genoemd worden. Een mooie integratie in Visual Studio brengt veel ontwikkelingsgemak met zich mee, gaande van een gemakkelijke Solution en Project gerichte aanpak tot integratie met Visual Studio als slagroom op de taart. Maar niet alleen de buitenkant is gewijzigd, ook wat in de taart zit smaakt beter. Vele nieuwe taken maken dat de meeste opdrachten nu gemakkelijker en overzichtelijker kunnen ontwikkeld worden, zonder dat er scripts moeten geschreven worden. Door artificiële intelligentie te integreren behoort automatische data cleansing tot de mogelijkheden dankzij taken zoals Fuzzy Lookup en Fuzzy Grouping. En tot slot wordt het bakken van de taart ook gemakkelijker gemaakt dankzij allerlei debug en deploy mogelijkheden. Voor al dit lekkers, rep je je naar je msdn subscription om daar de laatste release van SQL Server 2005 te downloaden. SQL Server Integration Services vormt een onderdeel van alle versies van SQL Server 2005, met uitzondering van SQL Server 2005 Express. Smakelijk.

 

Dr. Nico Jacobs

is trainer en consultant bij U2U (www.u2u.net), waar hij zich voornamelijk in Microsoft SQL Server en ADO.NET specialiseert. Zijn e-mailadres is nico@u2u.be.

 

Nuttige internetadressen

 

http://www.microsoft.com/sql/bi/integrate/productinfo/foresterreport.asp

http://www.sqlis.com/

http://www.datawarehouse.com/article/?articleid=5290