SSIS (Integration Services) als ETL Lösung – Dynamisches SSIS? [Coaches’ Hell]

Eine häufige Frage an mich ist, warum ich überhaupt so viel Zeit investiere in die Entwicklung von projektspezifischen SSIS Generatoren. Schließlich lasse sich doch in SSIS fast alles über Expressions und den Mechanismus der Paket-Konfiguration von außen dynamisch steuern! Wozu also der ganze Aufwand?

Die Aussage mit den Expressions (Ausdrücken) und der Konfiguration ist für sich genommen natürlich korrekt und wird von mir bzw. meinen Kollegen in Projekten auch verwendet.

Wichtig ist aber die Einsicht, dass sich halt nur “fast” alles damit steuern lässt! Und aufgrund der Architektur der Integration Services lässt sich während der Laufzeit eines Paketes der Inhalt eines Datenflusstasks nicht verändern! Und gerade da setze ich häufig an, um die Erstellung und Wartung von SSIS Paketen weitestgehend zu automatisieren.

Oft sehe ich aber Ansätze von Kunden oder in der Community für dynamisches SSIS, welche so nicht funktionieren können. Um solchen Missverständnissen vorzubeugen, möchte ich heute mal ein Beispiel zeigen, was eben gerade nicht funktionieren kann. Den Ansatz brachte neulich ein Kunde zum SSIS Coaching mit.

Also, sein Ansatz sieht vor, dass er zwei Variablen hat. Eine für den Namen einer Tabelle und einen für die SQL Query.

Dann kommt er mit der Idee, die Eigenschaft “EvaluateAsExpression” der Variable “SQL” zu nutzen, um die Query zusammenzusetzen. Damit hat die Variable den Value, welcher sich aus der Expression ergibt.

Nun kommt in einem Datenflusstask eine OLE DB Quelle, in welcher die Variable “SQL” verwendet wird. Alles gut, wir sehen die Query unten. Das scheint zu passen.

Den Datenfluss erweitern wir nun um einen Multicast, damit er lauffähig wird. Multicast kann ja durchaus aus mal zum Testen von Paketen verwendet werden da es “auch” ein NULL-Ziel sein kann. Noch ein Blick auf die Metadaten, die sind ja das A&O eines Datenflusstasks. Sind auch da. Immer noch alles gut!

Und wir lassen das Paket mal laufen. Wenige Sekunden später. Es funktioniert. Also, wozu die ganze Aufregung?

Kommen wir zum eigentlichen Zweck der Variablen. Ändern wir die Variable für die Tabelle ab, um eine andere Tabelle als Quelle zu verwenden. Ich habe hier gerade noch die Demotabelle für meinen RDLInput liegen, nehmen wir einfach die.

Und wieder starte ich das Paket. Und was passiert? Ein Fehler kommt hoch! Hier ist nämlich der Denkfehler versteckt. Und zwar gibt es ein Problem mit den Metadaten.

Schließen wir die Meldung und öffnen mal unsere Quelle. Und wieder kommt eine Meldung hoch. Und zwar möchte uns die Quelle mitteilen, dass die SQL Query (in der Variable) und die Metadaten der Quelle nicht mehr synchron sind. Also nicht mehr übereinstimmen. Klar, wir wollten nun ja auch eine andere Tabelle haben und natürlich hat diese andere Spalten. Selbst das automatische Beheben des Fehlers würde nur die Quelle “heilen”, aber die weiteren Datenflusselemente hätten ebenfalls mit den Metadaten zu kämpfen. Also stände uns viel manuelles Doing bevor, um das alles wieder anzupassen. Die dynamische Konfiguration hat sich also nicht gelohnt.

Wie kommt es nun dazu?

Die Architektur der Integration Services sieht vor, dass die Metadaten eines Datenflusstasks zur Entwurfszeit bekannt sind, damit alle der Quelle folgenden Elemente korrekte Input & Output Spalten haben können. Wenn also während der Laufzeit in die Metadaten eingegriffen wird, wie zum Beispiel durch das Neusetzen einer Variable, welche eine Query enthält, dann schützen sich die SSIS und werfen sofort einen Fehler! Wenn Pakete mit den Microsoft Bibliotheken per Repository erzeugt werden, dann befindet sich das Paket in dem Moment ebenfalls im Entwurfsmodus und es können die Metadaten korrekt zugewiesen werden. Das ist die Magie eine SSIS Paket Generators gegenüber der Konfiguration.

Wo dieser Ansatz aber sehr wohl funktioniert, ist eine Umgebung, in der zwar der Tabellenname sich ändern kann, aber die Metadaten (Spalten) identisch bleiben. Selten, kann es aber auch geben. Häufiger kommt eigentlich vor, dass der Connectionstring einer Quelle und eines Ziels mittels Konfiguration geändert wird, damit man zwischen Entwicklungs-, Test- und Produktivumgebung wechseln kann. Das macht durchaus Sinn! :-)