SQL Server Integration Services (SSIS) is de Microsoft implementatie voor ETL (Extract, Transform, Load) waarmee je complexe data-integratie- en transformatie-oplossingen kunt bouwen. Met behulp van de Business Intelligence Development Studio (BIDS) kan de ontwikkelaar met standaard bouwblokken een enterprise oplossing bouwen. Dit artikel beschrijft de belangrijkste bouwblokken van SSIS, gevolgd door een beschrijving van de verschillende fasen van het ETL-proces aan de hand van een voorbeeldapplicatie. Daarna zal er een greep gedaan worden uit een aantal praktijksituaties waar je als SSIS ontwikkelaar mee te maken kunt krijgen.
Een ETL-proces bestaat uit Tasks en pijlen die de flow aangeven
Standaard Bouwblokken
Bij het maken van een SSIS-applicatie heeft de ontwikkelaar een aantal standaard bouwblokken tot zijn beschikking. Een SSIS-solution bestaat uit één of meerdere uitvoerbare packages met daarin de functionaliteit om het ETL-proces te kunnen bouwen.
Deze functionaliteit bouw je op met tasks en pijlen die de flow tussen tasks aangeven. De structuur van de package wordt beschreven in de control flow en het werkelijke inlezen, transformeren en laden van de data gebeurt in de data flow. Connection managers zijn de verbinding tussen een bron of doel en worden door de tasks gebruikt om data in te lezen resp. weg te schrijven. Je kunt de packages configureren met zgn. package configurations, en je kunt gebruik maken van event handlers voor het afhandelen van verschillende gebeurtenissen.
Fig. 1 : Overzicht van SQL Server Integration Services
SSIS voorbeeldapplicatie
De voorbeeldapplicatie in dit artikel verzamelt elke week de kosten die zijn gemaakt voor de verschillende projecten binnen een bedrijf. Projecten worden uitgevoerd op diverse locaties in de wereld, waardoor kosten in verschillende munteenheden worden uitgedrukt.
De manager van het bedrijf wil elke week een rapport hebben waarin alle projectkosten gesommeerd zijn uitgedrukt in euro’s. De wekelijkse kosten van alle projecten worden aangeleverd in een CSV bestand met meerdere regels per project. SSIS genereert hier een rapport van waarin de projectkosten per land en per project gegroepeerd zijn.
Fig. 2 : Schematische weergave van de SSIS voorbeeldapplicatie
Fase 1: Extract
In de eerste fase van het ETL-proces leest de package de brondata in. SSIS kan de brondata van verschillende soorten bronnen lezen, zoals een externe SQL Server database, een SAP-systeem of in ons geval een CSV-bestand. De data die het ETL-proces inleest, moet betrouwbaar zijn voor het doel en dus is het belangrijk om de kwaliteit van de brondata te valideren. Voorbeelden van validatie zijn de controle op de lengte van een reeks karakters, de controle van het data type of op een onder- en bovengrens. Een ETL-oplossing maakt vaak gebruik van een staging area, een database die je gebruikt voor de tijdelijke opslag van data gedurende alle fases van het ETL-proces. Deze database kan data bevatten van verschillende bronnen, ingelezen op verschillende tijdstippen, die wordt gegroepeerd tot één resultaat. Om de opgehaalde data door het gehele proces te kunnen volgen kun je een Lineage ID veld gebruiken, een unieke reeks van karakters die wordt gebruikt om een set van data uniek te maken. Dit is zeer bruikbaar voor audit doeleinden, foutafhandeling, systeemherstel en het gemakkelijk kunnen selecteren van data tussen de verschillende stappen in het ETL-proces.
In de voorbeeldapplicatie gebruiken we een Flat File Source task om brondata uit een CSV-bestand te lezen. Het ETL-proces valideert tijdens het ophalen van de data de waarden op datatype en of deze het juiste aantal karakters bevatten. Ook wordt de Lineage ID toegevoegd aan de dataflow om de gehele set van data uniek te maken.
Fig. 3 : De technische en functionele validaties die worden uitgevoerd tijdens de Extract fase
Voor de validatie maak je gebruik van Data Conversion tasks. Voor het voorbeeld is gekozen om elk type validatie in een aparte task onder te brengen, waardoor de applicatie overzichtelijk is en gemakkelijk onderhoudbaar blijft.
Fig. 4 : De datatype- en lengte-validatie
Met een Derived Column task kun je de Lineage ID aan de datastroom toevoegen.
Fig. 5 : Het toevoegen van de Lineage ID kolom aan de datastroom
De OLE DB Destination task laadt de gereedstaande data in de SourceData tabel in de staging area. Deze eerste tabel die de opgehaalde data bevat, wordt de “landing” tabel genoemd.
Fase 2: Transform
De tweede fase van het ETL-proces is de transformatie van de brondata. Het komt vaak voor dat de brondata niet in het juiste formaat wordt aangeleverd. Om de data betekenis te geven voor het doelsysteem moet je deze data transformeren. Veel voorkomende taken die je hier voor kunt gebruiken zijn het opzoeken van waarden, het doen van berekeningen, het groeperen van data, het uitvoeren van functionele validaties en het toepassen van business rules.
Tijdens de transformatiefase converteert de voorbeeldapplicatie de kosten van de verschillende projecten naar euro’s en sommeert deze kosten vervolgens.
Met een Lookup task zoekt het proces de wisselkoers op, waarna het de kosten omzet naar euro met een Derived Column task. Selecteer altijd alleen de velden (dus geen ‘SELECT *’) die je gebruikt voor het opzoeken; dit kan veel performancewinst geven. Om de kosten per land en per project te sommeren gebruik je de Aggregate task.
Fig. 6 : Een Lookup task die de wisselkoers (Rate) bij een bepaalde eenheid opzoekt
Fig. 7 : De conversie van de kosten naar de eenheid Euro (kosten x wisselkoers)
Fig. 8 : Sommeren van de kosten per land, per project
Fase 3: Load
De derde fase van het ETL-proces is het wegschrijven van de data naar een doel, zodat het bruikbaar is voor andere gebruikers en systemen. Het formatteren van de data speelt hierbij een belangrijke rol.
De voorbeeldapplicatie genereert een rapport dat de kosten laat zien per project en per land. Het rapport bevat de namen van de landen en projecten en de daarbij behorende kosten afgerond op twee decimalen.
Fig. 9 : De kosten worden afgerond op twee decimalen
Het proces schrijft de geformatteerde data met een Flat File Destination task weg naar een rapport in CSV-formaat.
Foutafhandeling
Tijdens het ETL-proces kunnen zich allerlei fouten voordoen. Om deze fouten op een goede manier af te kunnen handelen is het noodzakelijk om deze fouten te loggen en de verantwoordelijke gebruikers op de hoogte te brengen. Hierbij maken we onderscheid tussen functionele en technische fouten, onderstaande tabel geeft kort de kenmerken van beiden.
| Functionele fouten |
Technische fouten |
| Business gerelateerd |
Systeem gerelateerd |
| De business gebruikers zijn verantwoordelijk voor de afhandeling (mogelijk met de tussenkomst van de systeembeheerder) |
De systeembeheerder is verantwoordelijk voor de afhandeling |
| Uitkomst van functionele validaties |
Deze fouten activeren de OnError event handler |
| Fouten worden gelogd in een tabel |
Fouten worden gelogd in de Windows Application Eventlog |
| De gebruikers worden op de hoogte gebacht middels een e-mail |
De beheerder wordt op de hoogte gebracht middels een e-mail |
| Voorbeelden: een waarde heeft een onjuist datatype, een waarde ligt buiten de vereiste grenzen |
Voorbeelden: database verbinding verbroken, account gegevens verlopen |
Omdat fouten op meerdere plaatsen in het ETL-proces kunnen voorkomen, moet het proces de fouten verzamelen en pas op het eind een e-mail versturen. De e-mail kan een lijst met alle fouten bevatten of een verwijzing naar een locatie waar de fouten kunnen worden bekeken en afgehandeld.
Fig. 10 : De gegenereerde fouten worden verzameld en vervolgens per e-mail verstuurd
Met SSIS is het een uitdaging om de veldnaam te achterhalen van de onconverteerbare waardes
De Extract-fase valideert een aantal velden op datatype met een Data Conversion task. Wanneer het proces een waarde niet kan converteren, stuurt het de rij door naar de error output. Om een duidelijke omschrijving van de fout te kunnen maken moet je de veldnaam in de beschrijving opnemen. Met SSIS is het echter een uitdaging om de veldnaam te achterhalen van de waarde die het niet kon converteren. De error output van de Data Conversion task stuurt onder andere de numerieke waarde van de ErrorColumn door, wat een verwijzing is naar het veld waarvan de conversie niet lukte. Met deze ErrorColumn waarde ben je in staat de bijbehorende veldnaam op te zoeken waarbij je gebruik kan maken van één van de volgende oplossingen.
De eerste optie is een Derived Column task. Zo’n task is eenvoudig te maken en snel te implementeren. Helaas is een expressie onoverzichtelijk en heeft deze een afhankelijkheid op de relatie tussen ErrorColumn en veldnaam. De “ID” waarde van een veldnaam (opzoeken in Input and Output Properties tabblad) komt overeen met de ErrorColumn die de Data Conversion task naar buiten stuurt. Deze “ID” waarde kun je in de expressie van de Derived Column task gebruiken om de relatie ErrorColumn – veldnaam te leggen.
Fig. 11 : De relatie tussen de ErrorColumn waarde en de bijbehorende veldnaam
Een andere optie is het opzoeken van een naam in een XML-bestand dat de package beschrijft. Hier ben je onafhankelijk van de relatie tussen ErrorColumn en veldnaam. Wel moet je een script bouwen om het XML-bestand te doorzoeken en ben je afhankelijk van de locatie van het XML-bestand. Selecteer het element “outputColumn” waarvan het attribuut “id” overeenkomt met de waarde van de ErrorColumn; het attribuut “name” bevat de veldnaam.
<outputColumn id=”148” name=”Expenses” …
Een laatste mogelijkheid is een tabel met de ErrorColumn en veldnaamgegevens, waardoor je een duidelijk overzicht hebt van de relatie tussen ErrorColumn en veldnaam. Hier ben je weer afhankelijk van wijzigingen in de relatie tussen ErrorColumn en veldnaam. Zoek de “ID” waarde van een veldnaam op in de Input and Output Properties tabblad en schrijf deze in een tabel. Met een Lookup task kan je aan de hand van de ErrorColumn de bijbehorende veldnaam opzoeken.
Fig. 12 : Lookup tabel
Variabelen
Zoals we gewend zijn bij programmeertalen, kunnen ook binnen packages variabelen gedeclareerd worden. Het gebruik van variabelen wordt interessant als deze ook gebruikt worden in child packages.
Een variabele die je declareert in een parent package kan je met package configurations doorgeven aan een child package; daarbij wordt een kopie van de waarde doorgegeven. In de child package moet je een variabele declareren die de kopie van de doorgestuurde waarde bewaart.
Fig. 13 : M.b.v. package configurations wordt een waarde doorgegeven naar de child package
Als het ene child package de resultaten van een andere child package wil gebruiken, los je dat op door:
- Declareer in de parent package een variabele (ParentVariable);
- De eerste child package kent een waarde toe aan de variabele;
- De parent package geeft de aangepaste waarde van variabele met package configurations door aan de tweede child package.
Fig. 14 : De variabele wordt gevuld door child package 1 en vervolgens gelezen door child package 2
Installatie (bestandssysteem of SQL Server)
SSIS kan een package vanaf het bestandssysteem of een SQL Server instantie laden. Tijdens het bouwen van packages staan de packages op het bestandssysteem, terwijl de packages in de productie omgeving in een SQL Server instantie staan. Bij het aanroepen van child packages vanuit een parent package, gebruikmakend van een Execute Package task, dien je rekening te houden met de fysieke omgeving waar de child package is opgeslagen. Voor een child package dat SSIS vanaf het bestandssysteem laadt, is namelijk een ander type connection manager nodig dan voor een SQL Server instantie. Dit is eenvoudig op te lossen met een variabele, gekoppeld aan package configurations, en door het definiëren van de expressies Connection en PackageName van de Execute Package task. In deze expressies selecteer je afhankelijk van de waarde van de variabele de juiste connection manager.
In de voorbeeldapplicatie is de variabele IsProduction gedeclareerd als van het type Boolean dat aan de package configurations gekoppeld is. Er zijn twee connection managers toegevoegd, één File connection manager die verwijst naar de package ExtractData.dtsx op het bestandssysteem en één OLE DB connection manager die verwijst naar een bepaalde SQL Server instantie. De Execute Package task “ExtractData” voert een child package uit waarvan de expressies Connection en PackageName zijn gedefinieerd.
Fig. 15 : De Connection en PackageName expressies van de Execute Package task
De Connection expressie selecteert de OLE DB connection manager (CM_SQL_ExtractData) of File connection manager (CM_FILE_ExtractData), afhankelijk van de IsProduction waarde. De PackageName expressie is leeg voor de File connection manager, en bevat de naam van de package voor de OLE DB connection manager.
Indien de package naam een punt bevat, moet je er rekening mee houden dat deze punten automatisch worden vervangen door spaties wanneer je deze packages op een SQL Server instantie installeert. Een best practice is dan ook om geen punten te gebruiken in package namen.
Mee leren leven...
En dan zijn er nog zaken waar de wenkbrauwen van menig SSIS-ontwikkelaar van gaan fronzen, maar waar we mee moeten leren leven:
- Packages kun je voorzien van commentaar met annotations. Deze annotations kun je helaas niet kopiëren en ze zijn niet gekoppeld aan tasks;
- Indien je een aantal keurig uitgelijnde tasks van de ene package naar een andere package kopieert, verandert de layout van deze tasks in de doel package;
- Als je de metadata van een package met meerdere tasks verandert (er veranderen bijvoorbeeld datatypes van bepaalde velden), zul je de meeste tasks moeten verversen. De Union task is echter een vreemde eend in de bijt, omdat je deze task niet kunt verversen. De enige manier om dit op te lossen is door de task te vervangen door een nieuwe;
- In de control flow zijn er genoeg situaties te bedenken die simpel met een if-then-else opgelost zouden kunnen worden. SSIS bevat helaas geen if-then-else of case task. Een workaround is om een Script task met bijbehorende Constraints en expressies te gebruiken of een For Loop Container task waarin de EvalExpression expressie de oplossing biedt;
- In de control flow kun je Script tasks debuggen, zoals elke ontwikkelaar gewend is (breakpoints, watches, etc.). In de data flow kun je de Script componenten echter niet debuggen waardoor allerlei trucs nodig zijn, zoals door gebruik te maken van de oude, vertrouwde messagebox of door de fouten middels een error output naar buiten te sturen;
- SSIS ondersteunt het .NET Framework, maar als C# ontwikkelaar heb je alléén de keuze uit Visual Basic.NET. Gelukkig kan er in SQL Server 2008 wel naar hartelust in C# gebouwd worden.
Conclusie
Als doorgewinterde .NET ontwikkelaar krijg je bij het bouwen van een SSIS applicatie al vrij snel het gevoel dat je beperkt wordt in je bewegingsvrijheid. Met een aantal standaard bouwblokken moet de gehele functionaliteit worden gebouwd. Echter, als je eenmaal aan het bouwen bent met SSIS, heb je snel iets werkends in elkaar gezet en leer je vrij snel waar welke trucs nodig zijn om bepaalde uitdagingen op te lossen. Tijdens het ontwikkelen kom je zo nu en dan situaties tegen waarin je simpele functionaliteit op een vrij omslachtige manier moet bouwen; de Script task en het Script component bieden hier vaak een uitkomst. Gelukkig zijn er veel bedrijven die al dan niet open source SSIS componenten aanbieden waardoor deze omslachtige methoden niet meer nodig zijn. Ondanks dat SSIS hier en daar nog zijn eigenaardigheden heeft, is SSIS uitermate geschikt om op een productieve manier en snel een goede ETL-oplossing te bouwen.
Referenties
Op het internet is ontzettend veel informatie te vinden over SSIS; hieronder zijn de belangrijkste bronnen bij elkaar gezet.