This post is also available in: en

Aufrufe: 316

Vor zwei Jahren habe ich einen Artikel geschrieben, wie Daten aus Power BI Desktop in eine SQL Datenbank exportiert werden können. In der Zwischenzeit haben mehrere andere Blogautoren wie zum Beispiel Leila Etaati von Radacad das Thema auch entdeckt und zum Teil noch weiterentwickelt. Auch Ruth von Curbal hat darüber ein Video veröffentlicht.
Ein Punkt hat mich aber immer ein bisschen an der Lösung gestört: die Performance kann bei grossen Datensets sehr schlecht sein, was auch Soheil Bakshi zu recht moniert hat. Aber jetzt gibt es eine weitere Option, welche die Möglichkeiten von Power Query in SSIS direkt nutzen kann.

Wenn Du mehr über Power BI lesen willst, so abonniere unseren Newsletter.


Kurzvorstellung SSIS

SSIS steht für SQL Server Integration Services und ist das, wie ich finde, zwar universelle, aber auch schon etwas angestaubte, Datenimport Tool für Microsofts SQL Server. Seit Power Query auf dem Markt ist, ist meine Liebe zu SSIS immer kleiner geworden… Sorry Microsoft, ist aber so. Das Interface ist nicht mehr modern, dafür sind die Möglichkeiten, Importe von Daten in einer bestimmten Reihenfolge durchzuführen, nicht zu verachten. Etwas, dass Power Query nach wie vor fehlt.

Vor kurzem hat Microsoft bekannt gegeben, dass nun endlich Power Query in SSIS integriert werden soll. Die Artikel dazu findet ihr hier und hier und natürlich hat Chris auch schon darüber geschrieben….. Chris, wann schläfst Du eigentlich mal?? Wie auch immer…

Power Query in SSIS ist endlich da!

Was SSIS sonst noch kann, dazu verweise ich Euch gerne auf entsprechende Einführungsartikel und Videotrainings, von denen es im Internet unzählige gibt (zum Beispiel in den Microsoft Docs).

Power Query in SSIS

Um Power Query in SSIS nutzen zu können, braucht ihr drei Dinge:

Das ist wirklich schon alles!

Arbeitsschritte in Power Query

Im Moment (Mai 2019) verfügt Power Query in SSIS nicht über die gewohnte Benutzeroberfläche und es kann nur der M-Code erfasst werden.
Erstellt daher eine kleine Abfrage in Power Query, wie zum Beispiel den Import einer csv-Dateien.
Im Beispiel habe ich die Datei “Contoso Products” importiert und ein paar Transformationen vorgenommen.

M-Code Import Contoso-Products
M-Code Import Contoso-Products

†Power Query in SSIS

Wechselt nun in die SSDT und erstellt ein neues SSIS Paket (Anleitung).

Erstellen eines SSIS Pakets
Erstellen eines SSIS Pakets

Als nächstes zieht im neuen Fenster einen Datenflusstask aus der SSIS-Toolbox in den grauen Bereich rechts.

Erstellen eines Datenflusstasks
Erstellen eines Datenflusstasks

Mittels Doppelklicks auf den neuen Task im grauen Bereich öffnet sich das nächste Fenster, der Datenfluss.
Dort seht ihr links in der SSIS-Toolbox einen neuen Task: Power Query Source

M Code einfügen

Power Query Source im Datenfluss
Power Query Source im Datenfluss

Klickt nun, wieder mit Doppelklick, auf das neue Feld rechts und fügt den M-Code der Power Query Abfrage hier ein. Den Query Mode lassen wir auf «Single Query». Die andere Option wäre «Single Query from Variable».

M-Code in SSIS
M-Code in SSIS

Verbindungen herstellen

SSIS benötigt immer Informationen, um die Verbindung sowohl zur Quelle als auch zum SQL-Server als Ziel herstellen zu können.
Wechselt daher in den Connection Manager. Hier müssten wir normalerweise den ganzen Connection String zur Datenquelle erfassen und auch noch einen Connection Manager anlegen. Aber nicht mit Power Query!
Da wir im M-Code bereits alle Angaben erfasst haben, können wir SSIS die Arbeit zum Teil gleich übernehmen lassen. Klickt dazu auf «Detect Data Source».

Verbindung zur csv Datei erstellen
Verbindung zur csv Datei erstellen

Schon mal gar nicht schlecht. Aber trotzdem erhalten wir einen Warnhinweis.

Connection Manager fehlt noch
Connection Manager fehlt noch

Es fehlt noch der Connection Manager. Klickt dazu in das Feld «Connection Manager» und wählt «New Connection».
Und SSIS liest aus dem M-Code den dazugehörigen Dateipfad aus. Automatisch. Das Leben kann so schön sein.

Connection Manager erstellen
Connection Manager erstellen

Testet noch die Verbindung und falls alles funktioniert, können wir nun die Daten in eine neue (oder bereits vorhandene) SQL Tabelle laden.

SQL Tabelle erstellen

Dazu benötigen wir einen Ziel-Assistenten (1), wählen danach als Option den SQL-Server (2) und klicken auf «Neu» (3). Er erscheint der Verbindungsmanager, wo wir den SQL Server (4) sowie die Datenbank (5) erfassen. Testet noch die Verbindung (6) und quittiert die Meldung mit «OK» (7).

SQL Ziel erstellen
SQL Ziel erstellen

Nun habt ihr zwei Objekte auf dem Bildschirm. Zieht den blauen Pfeil von «Power Query Source» auf «OLE DB Ziel». Ihr könnt übrigens mittels Doppelklicks auf die beiden Tasks einen anderen Namen vergeben.

Quelle zum Ziel verbinden
Quelle zum Ziel verbinden

Da wir noch ein rotes Kreuz beim Ziel haben, öffnen wir dieses mit…. Doppelklick, what else…

Noch fehlt uns eine Tabelle, was SSIS uns auch mitteilt.

fehlende SQL Tabelle erstellen
fehlende SQL Tabelle erstellen

Klickt auf «Neu». SSIS macht uns einen Vorschlag, wie die neue Tabelle mit einem T-SQL Statement erstellt werden kann.

CREATE TABLE
CREATE TABLE

Und das Tolle ist… SSIS hat aus dem M-Code die Datentypen bereits in SQL-Datentypen umgewandelt. Wir müssen also keine mühsamen Transformationsschritte mehr vornehmen. Das Leben kann so schön sein.

Klickt auf «OK» und die Tabelle wird in der SQL Datenbank erstellt. Leider fehlt hier eine Bestätigungsmeldung, aber die Tabelle wird wirklich erstellt.
Und als letzter Schritt teilen wir SSIS noch mit, welche Spalte aus dem M-Skript zu welcher SQL-Tabellenspalte gehört. Aber auch hier hilft uns SSIS und versucht selbst, die entsprechende Zuordnung vorzunehmen. Wechselt dazu in den Reiter «Zuordnungen».

Spaltenzuordnung
Spaltenzuordnung

Unten seht ihr links jeweils die Eingabespalten aus dem M-Code und rechts die entsprechenden SQL-Tabellenspalten. Klickt auf «OK» und das rote Kreuz auf dem «OLE DB Ziel» verschwindet. Falls das Mapping nicht korrekt sein sollte, so könnt ihr dies hier anpassen indem ihr unten auf die entsprechende Spalte klickt und den korrekten Eintrag aus der Dropdownliste wählt.

Testen der Lösung

Und nun testen wir das Ganze und führen das SSIS Paket aus. Oben in der Mitte findet ihr einen grünen Pfeil, welcher die Lösung startet. Klickt einmal auf den Pfeil.

SSIS Paket starten
SSIS Paket starten

Nach kurzer Zeit (in diesem Fall waren es 2.156 Sekunden…), hat SSIS die Daten aus der csv Datei extrahiert, umgewandelt und in die SQL-Tabelle gespeichert. Das Leben ist schön.

Success!
Success!

Als Beweis dafür noch ein Screenshot aus dem SQL Server Management Studio.

Daten in der Tabelle
Daten in der Tabelle

Schlussfolgerung und Ausblick

In diesem Beitrag habe ich gezeigt, wie Power Query das Leben immer mehr vereinfacht. Dank dem Power Query Task ist es nun möglich, Lösungen zuerst in einem Power Query Editor zu entwickeln und danach die Daten mittels SSIS in einer SQL Datenbank zu speichern. Nach Bereitstellung des SSIS Pakets kann die Lösung immer wieder verwendet werden.

Auch können mühsame Tasks in SSIS mittels Verwendung von M-Code sehr einfach gelöst werden. Im Weiteren können auch die ganzen Orchestrierungsmöglichkeiten von SSIS wie zum Beispiel das sequentielle Einspielen von Abfragen in einer definierten Reihenfolge genutzt werden.

Oder die SSIS Pakete können mit den SQL Server Agent Jobs zeitgenau angesteuert werden. Oder sie können auch mit Stored Procedures von ausserhalb (zum Beispiel aus…. Power Query) gestartet werden.

Auch sind umständliche Workarounds wie das Finden einer Power BI Desktop Port Nummer nicht mehr für jeden Fall erforderlich.

Was für mich im Moment noch fehlt, sind zwei Dinge:

  • Power Query Oberfläche in SSIS – im Moment muss der M-Code eingefügt werden, was sicherlich nicht für jeden Benutzer geeignet ist.
  • Die Möglichkeiten von SSIS für den inkrementellen Datenimport in die Power BI Dataflows…. Man darf aber träumen.

Zum Schluss bleibt mir nur noch eines: Jetzt mag auch ich SSIS wieder.

Mehr über Power BI könnt ihr hier nachlesen.

Happy Querying!

Wenn Du mehr über Power BI lesen willst, so abonniere unseren Newsletter.


Ein Gedanke zu „Power Query in SSIS

Kommentar verfassen

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.

%d Bloggern gefällt das: