Wer Daten mit DeltaMaster analysieren möchte, muss natürlich zuerst einmal dafür sorgen, dass überhaupt Daten zum Analysieren da sind. In unseren Projekten setzen wir sehr oft auf Microsoft SQL-Server mit Analysis Services, um die Daten aus den unterschiedlichsten Vorsystemen zusammen zu tragen, aufzubereiten und für eine optimale Datenanalyse bereit zu stellen. Aber nicht alle Systeme können direkt über eine Schnittstelle “angezapft” werden. In diesen Fällen muss eine andere Lösung für den Datenimport gefunden werden. Eine Möglichkeit ist dann, die Daten aus dem Vorsystem in ein Flatfile zu exportieren und mit den Bordmitteln der SQL-Server Integration Services (kurz SSIS) in den SQL-Server automatisch zu importieren.
“Flatfiles” sind einfache Textdateien (.txt, .csv). Allen gemeinsam ist, dass die Daten nach einem vorgegebenen Schema spaltenweise geordnet sind, sodass sie in eine Tabelle importiert werden können. Um die einzelnen Spalten voneinander abzugrenzen, werden entweder feste Spaltenbreiten vorgegeben – hierbei werden die Daten mit Leerzeichen aufgefüllt, bis die notwendige Spaltenbreite erreicht ist – oder es werden Trennzeichen verwendet (z.B. TAB, Komma, Semikolon etc.). Bei der Wahl des Trennzeichens ist besonders wichtig, dass es nicht auch in den Daten enthalten ist. Wird z.B. ein Komma als Spalten-Trennzeichen in einem Flatfile verwendet, in dem auch Dezimalzahlen mit Komma als Dezimaltrennzeichen vorkommen, führt dies beim Import zu schwerwiegenden Fehlern – plötzlich werden die Zahlen der Nachkommastellen als Inhalt der nächsten Spalte interpretiert… Das ist nicht gut!
Schauen wir jetzt zuerst auf den reinen Datenimport aus einem Flatfile mit SSIS. Als Basis verwenden wir die Datei Kunden_01.csv, die neben allen anderen Beispieldateien auf der Blog-Seite verfügbar ist.
Nachdem ein neues SSIS-Projekt im Business Intelligence Development Studio angelegt ist (Datei – Neu – Projekt… – Business Intelligence Projekte – Integration Services Projekt), kann aus der Toolbox auf der linke Seite das Objekt “Datenflusstask” per Drag-and-drop oder per Doppelklick in die Ablaufsteuerung des bereits geöffneten SSIS-Pakets Package.dtsx hinzugefügt werden.
Abb. 1: Einfügen eines neuen Datenflusstasks
Um den Datenimportprozess aufzubauen, muss in den Dialog Datenfluss gewechselt werden. Entweder durch einen Doppelklick auf das eingefügte Datenflusstask-Objekt oder über den Tab Datenfluss. In diesem Dialog wird der Flatfile-Import definiert. Man braucht mindestens zwei Objekte in diesem Tab: eine Datenflussquelle und ein Datenflussziel. Diese findet man in der Toolbox am linken Rand der Oberfläche. Wir verwenden für den Import der CSV-Datei natürlich die Flatfilequelle und als Ziel das SQL Server-Ziel. Ist die Flatfilequelle zur Datenflusstask hinzugefügt, kann durch einen Doppelklick auf das Objekt der Quellen-Editor für Flatfiles geöffnet werden. Hier muss zuerst ein neuer Verbindungsmanager für die Flatfilequelle angelegt werden (auf Neu… klicken). Im Verbindungsmanager-Editor müssen die Einstellungen wie gezeigt vorgenommen werden. Wichtig ist die Einstellung der Codepage – die muss zum Inhalt der Flatfiles passen, sonst werden nur kryptische Zeichen importiert – im Zweifelsfall sollte man die Flatfiles im Unicode-Format erstellen lassen. Insbesondere der Haken bei Spaltennamen in der ersten Datenzeile ist wichtig, da ansonsten die Spaltenüberschriften auch als Daten interpretiert werden.
Abb. 2: Anlegen eines neuen Flatfile-Verbindungsmanagers
Auf der linken Seite kann man durch Anklicken eine Spaltenvorschau erhalten und in den erweiterten Einstellungen festlegen, welche Datentypen für die einzelnen Spalteninhalte angewendet werden sollen. Es empfiehlt sich, die Daten aus dem Flatfile zuerst im Textformat zu belassen und die Spalten mit Zahlen erst in einem nachgelagerten Transformationsschritt umzuwandeln. So kann man sicher nachvollziehen, ob ein Wert nicht korrekt importiert wurde oder ob bei der Transformation ein Fehler aufgetreten ist.
Die offenen Dialoge mit OK verlassen und den grünen Pfeil von der Flatfilequelle über das SQL-Server-Ziel ziehen.
Durch Doppelklick auf das SQL-Server-Ziel den Ziel-Editor für SQL öffnen und einen OLE DB-Verbindungs-Manager anlegen (zweimal auf Neu… klicken).
Abb. 3: Anlegen eines neuen OLEDB-Verbindungsmanagers
Zweimal auf OK klicken und dann im Ziel-Editor für SQL neben Tabelle oder Sicht verwenden auf Neu… klicken. Im folgenden Dialog erscheint ein Create Table Statement, mit dem eine Tabelle erstellt werden kann, die genau zu den Spalten und eingestellten Datentypen des Flatfiles passt. Einzig der Tabellenname muss noch angepasst werden. Nach dem Klick auf OK wird die Tabelle angelegt.
Abb. 4: Automatisch generiertes CREATE TABLE-Statement zum Anlegen der Import-Tabelle
Der nächste Schritt im Ziel-Editor für SQL ist die Prüfung der Spaltenzuordnungen zwischen der Flatfilequelle und der Zieltabelle. Gleiche Namen werden automatisch erkannt und verknüpft, der Rest muss von Hand zugeordnet werden.
Abb. 5: Spaltenzuordnung im Ziel-Editor für SQL
Jetzt ist das SSIS-Paket bereits lauffähig und kann getestet werden. Einfach im Datenfluss-Fenster mit der rechten Maustaste auf den Hintergrund klicken und Task ausführen wählen.
Abb. 6: Erfolgreicher Testlauf des Datenimports
Wunderbar – der Import funktioniert und den Debug-Modus kann man ganz einfach wieder verlassen, wenn man in der Mitte des Bildschirms auf den Link “Die Paketausführung ist abgeschlossen……” klickt!
Aber bis jetzt können wir nur eine einzige Datei mit dem fest vorgegebenen Namen Kunden_01.csv importieren. Das kann reichen – muss es aber nicht. Was ist zu tun, wenn man nicht nur eine Datei, sondern mehrere zur gleichen Zeit bereitgestellt bekommt (z.B. Länderspezifische Produktinformationen, die pro Land geliefert werden) oder wenn im Dateinamen Zusätze enthalten sind, die sich ständig ändern (z.B. das Datum des Abzugs aus dem Vorsystem)?
Für diese Fälle hält die SSIS-Toolbox ein starkes Werkzeug bereit:
Foreach-Schleifencontainer (oder auch Foreach-Loopcontainer)
Mit dem Foreach-Schleifencontainer ist es möglich über eine beliebige Menge von Dateien zu iterieren und die Dateien zu verarbeiten. Dabei können Suchmuster für die Dateinamen vorgegeben werden.
Auf der Basis des voran gegangenen Datenimports werden wir nun der Einsatz des Foreach-Loopcontainers erläutern.
Bisher haben wir für die Flatfilequelle den Dateinamen fix im Verbindungsmanager eingetragen. Wenn jetzt mit dem Foreach-Loopcontainer über mehrere Dateien iteriert werden soll, dann müssen die Dateinamen über eine Variable vom Loopcontainer an den Verbindungsmanager übergeben werden.
Zum Anlegen einer Variablen muss der Dialog Variablen geöffnet werden. Das geht über einen Rechtsklick auf den Hintergrund in der Ablaufsteuerung und dem Datenfluss des SSIS-Pakets oder über das Menü SSIS – Variablen. Es öffnet sich ein Dialog, in dem die vorhandenen Variablen aufgeführt werden und neue angelegt werden können. Für die Übergabe von Dateinamen inkl. Pfad legen wir die Variable strFileName an. Hierbei ist darauf zu achten, dass der Bereich auf Package steht. Je nachdem, welches Objekt beim Anlegen der Variablen ausgewählt ist, wird der Bereich anders gesetzt. Die Variable strFileName sollte aber über das gesamte SSIS-Paket zur Verfügung stehen. Anschließend kann der Variablen-Dialog wieder geschlossen werden.
Abb. 7: Anlegen der Variablen strFileName
Jetzt kann der Foreach-Schleifencontainer aus der Toolbox in die Ablaufsteuerung gezogen werden (Tab: Ablaufsteuerung). Mit einem Doppelklick auf den Schleifencontainer wird der Foraeach-Schleifen-Editor geöffnet. Unter Auflistung kann der Ordner, der die Dateien enthält, angegeben und ein Suchmuster für den Dateinamen hinterlegt werden.
Abb. 8: Einstellungen für die Datei-Iteration
Um den Namen (incl. Pfad) der jeweils gefundenen Datei in der Variablen strFileName abzulegen, muss nur noch die Variablenzuordnung vorgenommen werden.
Abb. 9: Dateiname und -pfad einer Variablen zuordnen
Der Datenflusstask, der bereits im vorangegangenen Beispiel angelegt wurde, kann per Drag-and-drop in den Foreach-Loopcontainer gezogen werden, sodass bei jedem Schleifendurchlauf auch der Inhalt des Datenflusstasks ausgeführt wird. Was jetzt noch fehlt, ist die Zuordnung der Variablen strFileName an den Flatfile-Verbindungsmanager.
In den Eigenschaften des Verbindungs-Managers Flatfile_Kunden kann man sehen, dass die Pfadangabe der zu importierenden Datei in der Eigenschaft ConnectionString abgelegt ist.
Abb. 10: Eigenschaften des Verbindungs-Manager Flatfile_Kunde
Um jetzt den Wert der Variablen strFileName in den ConnectionString einzutragen, muss die Expressions-Liste gewählt werden. Ein direkter Eintrag der Variablen im ConnectionString würde nicht zum Erfolg führen.
Wenn im Eigenschaften-Fenster des Verbindungs-Managers der Eintrag Expressions ausgewählt wird, erscheint ein kleiner Button mit drei Punkten, über den man in den Einstellungsdialog für die Expressions gelangt. Hier muss nur noch dem ConnectionString die Benutzervariable strFileName zugeordnet werden. Damit werden alle Dateien aus einem Verzeichnis, deren Namen einem bestimmten Suchmuster entsprechen, nacheinander abgearbeitet.
Abb. 11: Variablenwert an die Eigenschaft ConnectionString des Verbindungs-Managers übergeben
Zum guten Schluss fehlt noch eine kleine Erweiterung, um die Dateien aus dem Import-Ordner in ein Archiv zu verschieben. Dazu wird der Task “Dateisystem” noch mit in den Foreach-Loopcontainer gepackt und mit dem Datenflusstask verbunden. Im Editor des Task “Dateisystem” muss unter Operation Datei verschieben eingestellt werden. Die Einstellungen IsSourcePathVariable und IsDestinationPathVariable müssen auf True gesetzt werden. Die SourceVariable ist natürlich die Variable die auf die aktuelle Datei zeigt – strFileName. Für die DestinationVariable muss erst noch eine Variable angelegt werden. Das kann man auch direkt über den Auswahl-Dialog machen. Der Name dieser Variable ist hier strArchivePath und die Variable enthält den String “.\Archive”. Jetzt muss im Import-Ordner nur noch manuell ein Verzeichnis namens Archive angelegt werden und dann werden die Dateien nach dem Import direkt in das Archive-Verzeichnis verschoben.
Abb. 12: Einstellungen für das Verschieben von Dateien
Natürlich ist damit noch längst nicht alles gesagt, was es zum Thema Flatfile-Import zu sagen gibt. Es fehlt noch die komplette Datenbereinigung, falls Daten geliefert werden, die bereits vorher schon einmal importiert wurden. Auch eine Fehlerbehandlung, die falsch gelieferte Datensätze herausfiltert und für eine spätere Nachbearbeitung archiviert, ohne dass der gesamte Importprozess fehlschlägt, wäre angebracht. Doch alles dies sind Themen für weitere Beiträge, die bestimmt folgen werden.
