Binnen SSIS bestaat de mogelijkheid om op een eenvoudige manier met Web Services te werken vanuit de Control Flow: de Web Service Task heeft de mogelijkheid om aan de hand van een WSDL-bestand een Web Service aan te roepen. Wanneer een beperkt aantal gegevens op deze manier opgehaald moet worden, voldoet dit prima. Denk hierbij bijvoorbeeld aan het ophalen van de actuele wisselkoers alvorens deze te gebruiken bij het verwerken van aangeleverde data.

Soms moet voor een verzameling data echter per data-eenheid (rij, bestand, veld, etc.) gegevens uit de Web Service onttrokken worden. In een applicatie die ik voor een klant ontwikkel moeten in de database gegevens gecontroleerd worden met behulp van een Web Service. De vraag rees of dit performance-wise wel haalbaar was om binnen SSIS te realiseren. Daarom heb ik een kleine Proof of Concept gebouwd waarbij circa 50,000 rijen gevuld waren met codes, en aangevuld moesten worden met berichten behorend bij de code. De tabel had de volgende structuur:

De kolom ‘msgTxt’ was initieel leeg, en moest gevuld worden op basis van de uitkomst van een Web Service en de waarde van msgId. Wanneer dit met de Web Services Task opgelost zou worden, zou het er als volgt uit kunnen zien:

Wat hier direct opvalt, is dat voor elke rij een enkele losse update uitgevoerd moet worden – een hoogst onwenselijke situatie. De werkelijke bottleneck zit echter in het aanroepen van de Web Service: elke keer wordt opnieuw verbinding gelegd, waardoor het enkele seconden duurt voordat het resultaat voor één rij opgehaald is.

Web Service Component

Het zou wenselijk zijn een soort ‘Web Service Component’ te kunnen gebruiken, dat zich binnen de Data Flow pijplijn bevindt. Weliswaar zou de ‘echte’ update daarmee niet meer mogelijk zijn, maar een soort staging zou in dit geval ook voldoen. Het voordeel van een component binnen de Data Flow is dat het slechts éénmaal aangeroepen wordt, en vervolgens daar alle rijen doorgevoerd worden (in tegenstelling tot de bovengenoemde oplossing, waarbij de Web Service Task voor elke rij aangeroepen werd, en er vervolgens slechts één rij verwerkt werd). Nog steeds is de vraag of SSIS geschikt is voor deze taak, maar het zou in elk geval een logische manier van uitwerken zijn, met een flinke mogelijke performance-winst.

In SSIS bestaat geen Web Service Component. Het is echter vrij eenvoudig zelf deze functionaliteit te implementeren met behulp van een Script Component. De Windows SDK biedt de mogelijkheid om een wrapper te genereren voor een WebService aan de hand van een WSDL-bestand. Deze wrapper (een object in C# of VB.NET) kan vervolgens gebruikt worden in de Script Task. De werkwijze is samen te vatten in drie stappen:

1. Wrapper (laten) maken door de Web Service
2. Aanmaken en configureren van een Script Component
3. Toevoegen van de wrapper aan het Script Component

Stap 1: Wrapper (laten) maken door de Web Service

Met behulp van de ‘wsdl.exe’ tool, die zich bevindt in de Windows SDK, is het eenvoudig om een simpele wrapper te maken voor een Web Service aan de hand van een WSDL. De syntax is te vinden op MSDN. In mijn geval zag het er als volgt uit:

wsdl /o:D:MeldingenWrapper.cs http://192.168.202.20/MeldingenService/Services/MeldingenService.svc?wsdl

Stap 2: Aanmaken van een Script Component

Nu de wrapper aangemaakt is, kunnen we het Script Component toevoegen dat de Web Service gaat gebruiken. Het Script Component wordt binnen een Data Flow Task geplaatst. Het is overigens handiger om eerst de Data Flow Source neer te zetten en in te stellen – het Script Component maakt gebruik van de inkomende kolomspecificaties. In mijn geval bestond de Data Flow Source uit een OLE DB Source, waar ik alle gegevens uit de bovengenoemde tabel haalde.

Het Script Component heeft de volgende configuratie nodig:

• Script Component Type: Transformation
• Input Columns: alle kolommen die doorgegeven moeten worden – zowel de kolommen die nodig zijn voor de aanroep van de Web Service als de kolommen die verderop in de Data Flow nog belangrijk zijn.

Vervolgens moeten de verrijkte kolommen nog toegevoegd worden. Let ook op de juiste datatypes!

Hiermee is het Script Component correct geconfigureerd en kan het script zelf bewerkt worden.

Stap 3: De wrapper invoegen in het Script Component

Het toevoegen van de wrapper aan het Script Component project is vrij eenvoudig:

1. De wsdl-tool heeft een enkel C#-bestand opgeleverd, dat we nu kunnen toevoegen aan het Script Component project d.m.v. ‘Add Existing Item’
2. Omdat de wrapper gebruik maakt van Web Services, moet de Reference ‘System.Web.Services’ moet toegevoegd worden
3. In de ScriptMain class voegen we de object-variabele toe die het object van de wrapper zal bevatten:

private MeldingenWrapper meldingenWrapper;

4. In de PreExecute-methode instantiëren we de wrapper-class, zodat deze gedurende de gehele pipeline blijft bestaan:

meldingenWrapper = new MeldingenWrapper();

5. In de methode [InputNaam]_ProcessInputRow(Input0Buffer Row) vullen we de extra kolommen d.m.v. de Web Service:

Row.msgTxt = meldingenWrapper.GetMelding((int) Row.msdId, true);

Hiermee is het Script Component klaar. Uiteraard is er nu nog niets gebeurd met de gegenereerde en gevulde kolommen – deze zullen nog steeds opgeslagen moeten worden in de database (of op een andere plaats).

Performance?

Het aanroepen van een Web Service per losse rij is een vrij dure operatie – met name wanneer het veel rijen betreft. Voor het gebruik dat de klant voor ogen had, was het echter ruim voldoende: 50,000 rijen werden verrijkt in circa 5 minuten (op de ontwikkel-omgeving waar de PoC geïmplementeerd was). Het betrof hier wel een Web Service op hetzelfde netwerk.

De performance is vrij traag in vergelijking met veel database-operaties, en heeft veel overhead door de communicatie over het netwerk voor elke doorgevoerde rij. Tegelijk blijft dit een enorme verbetering ten opzichte van de Web Service Task, en blijkt dat een belangrijke bottleneck weggenomen kan worden.

Conclusie

Met behulp van een Script Component blijkt het vrij eenvoudig te zijn om binnen SSIS een Web Service te gebruiken voor sets met rijen. Dit maakt SSIS niet direct de ideale tool om uitvragen via Web Services te doen: de Script Component past zich bijvoorbeeld niet aan aan veranderende WSDL-bestanden. Daarnaast is SSIS niet gericht op het uitwisselen van messages, maar op het doorvoeren van batches. Voor het doorvoeren van enkele verrijkingen met behulp van Web Services is dit echter een goed werkbare oplossing.