Mit dem ETL-Werkzeug SQL Server Integration Services (SSIS) des Microsoft SQL Servers können unterschiedlichste Datenladeprozesse umgesetzt werden. Dabei ist nicht nur die Zahl an anzubindenden Ziel- und Quellsystemen nahezu unerschöpflich, sondern auch die Datenmodifikations-, Datentransformations- und Datenvalidierungsmöglichkeiten, die zwischen der Anbindung der Quelle und des Ziels geschehen können. Und so ist es nicht verwunderlich, dass aus diesen vielen Modellierungsmöglichkeiten des Datenladeprozesses komplexe und verzahnte Pakete entstehen können, die anspruchsvolle Datenlogiken abbilden und beinhalten können.
In Projekten vertreten wir den Ansatz, die Datenanpassungen erst im Datawarehouse über eine View auf die importierten Daten vorzunehmen. Das bedeutet, dass wir den ETL-Prozess möglichst einfach halten und sehr wenige bis keine Datenmodifikationen in diesem Prozessschritt vornehmen. Das birgt viele Vorteile, denn zum einen wird der Ladeprozess als solches durch die geringe Komplexität stabiler, wartbarer und gleichzeitig werden die unter Umständen auftretenden Fehler im Ladeprozess leichter behebbar. Denn Fehler würden bei diesem Ansatz erst im „Transform“-Schritt des DeltaMaster Modelers auftreten. Hier sind sie dank sehr sprechender Fehlerbeschreibungen und Fehlerdetailberichten deutlich schneller auszumachen und zu beheben, als mit den oftmals sehr ungenauen und generischen Fehlermeldungen im SSIS-Prozess.
Aber auch simple Datenladeprozesse über SSIS haben ihre Tücken und können, ohne viel Logik zu enthalten, für einen hohen Frustrationsfaktor sorgen. Dies geschieht gefühlt besonders oft, wenn als Quelle Excel-Dateien angebunden werden. Dabei hängt es oftmals an einer ganz bestimmten Problematik, die zu Abbrüchen beim Ladeprozess führt. Für diese Problematik gibt es jedoch eine einfache und schnell umsetzbare Lösung, die wir Ihnen in diesem Blogbeitrag gerne zeigen möchten.
Problemstellung
Wir erstellen uns ein simples Datenladeprozessbeispiel über SSIS. In diesem wollen wir eine beispielhafte Excel-Tabelle per täglicher Laderoutine ins Datawarehouse laden. In unserem Fall sieht die Excel-Tabelle folgendermaßen aus:
Abb. 1: Excel-Datei für den Datenladeprozess
Um diese Datei in unser Datawarehouse hochzuladen, erstellen wir ein SSIS-Paket, welches unseren Datenladeprozess abbilden soll. Zunächst werden im Paket erst einmal die Verbindung zu Ziel und Quelle über den Verbindungs-Manager angelegt. Für die Excel-Tabelle erstellen wir unter „Neue Verbindung…“ eine Verbindung des Typen „Excel“ und wählen im Kontextmenü den Pfad zur Excel-Datei. Anschließend legen wir die Ziel-Verbindung an, indem wir uns via OLE-DB Verbindung mit dem SQL Server und der gewünschten Datenbank koppeln.
Nachdem die Verbindungen stehen, muss nur noch im Bereich der Ablaufsteuerung das Datenflusstask-Modul per Drag-and-drop hineingezogen werden und im Datenfluss die Module „Excel-Quelle“ und „OLE-DB-Ziel“. Nun verbinden wir die Quelle und das Ziel über den Datenflusspfad-Pfeil miteinander und ordnen ihnen noch die vorher erstellten Verbindungen des Verbindungsmanagers zu. Schlussendlich erhalten wir einen einfachen ETL-Prozess, der uns nun als Beispiel dienen soll.
Abb. 2: SSIS-Paket des Ladeprozesses
Um den Datenladeprozess zu automatisieren und in einem gewissen Zeitintervall ablaufen zu lassen, nutzen wir den SQL Server Agent und erstellen einen neuen Job. Dieser Job soll nur das zuvor erstellte SSIS-Paket beinhalten, welches dann durch das Dienstkonto des SQL Server-Agent ausgeführt wird.
Abb. 3: Job-Einstellungen
Unser Beispielladeprozess ist damit fertiggestellt und spiegelt vereinfacht die tägliche Datenladeroutine unseres Beispielunternehmens wider.
Nun überprüfen wir, ob der Ladeprozess funktioniert. Dafür starten wir den erstellten Job über das SQL Server Management Studio und achten darauf, dass die Excel-Datei nicht geöffnet ist. In diesem Fall läuft der Job sauber und ohne Fehler durch. (Wenn dem nicht so ist, überprüfen Sie bitte ob das Dienstkonto über Lese-/Ausführrechte auf dem Pfad des SSIS-Paketes und der Excel-Datei verfügt und ob Sie gegebenenfalls den Job in der 32-bit Laufzeitumgebung ausführen müssen.)
Abb. 4: Statusmeldung beim Ladeprozess
Wir wiederholen nun die Jobausführung mit dem kleinen Unterschied, dass wir nun zuvor die Excel-Datei öffnen und damit Zugriff haben. Das Ergebnis ist wiederum wenig erfreulich: Es tritt ein Fehler auf.
Abb. 5: Statusmeldung beim Ladeprozess mit Excel-Datei im Zugriff
Im Jobverlauf sehen wir folgende Fehlermeldung:
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER’. Fehler beim Aufrufen der AcquireConnection-Methode über den Verbindungs-Manager ‘Excel-Umsatz&Kosten’ (Fehlercode: 0xC0202009).
Die nicht ganz sprechende Fehlermeldung entsteht genau dann, wenn sich eine Excel-Datei bereits im Zugriff befindet und gleichzeitig der SQL Server Agent darauf zugreifen will. Für den SQL Server Agent ist dann kein Zugriff auf die Excel-Datei mehr möglich und es kommt zu einem Fehler, welcher dann wiederum zum Abbruch des Datenladeprozesses führt.
Lösung
In der Grundeinstellung wird eine Excel-Datei im exklusiven Zugriff geöffnet. Das bedeutet, die originäre Datei ist ab diesem Zeitpunkt im Zugriff und kann von keinem weiteren Benutzer geöffnet und bearbeitet werden. Öffnet man trotzdem eine bereits im Zugriff befindliche Excel-Datei, dann wird folgende Meldung angezeigt und man kann die Datei dann nur im schreibgeschützten Modus öffnen oder den Benutzer benachrichtigen.
Abb. 6: Hinweis bei im Zugriff befindlicher Excel-Datei
Bereits seit Excel 2003 (Hier unter Extras -> Arbeitsmappe freigeben) ist es möglich eine Excel-Datei für mehrere Benutzer freizugeben. Unter dem Reiter „Überprüfen“ befindet sich ab Excel 2007 die Option „Arbeitsmappe freigeben“, mit der Sie in das folgende Dialogfeld gelangen:
Abb. 7: Dialogfeld zum Freigeben der Arbeitsmappe
Durch das Setzen des Hakens ist die Excel-Datei nach der Bestätigung und der darauffolgenden Speicherung für den Mehrbenutzerbetrieb ausgelegt. Dies erkennen Sie zudem durch das Suffix „[Freigegeben]“ hinter dem Dateinamen in Excel. Damit können mehrere Benutzer gleichzeitig auf dieselbe Arbeitsmappe zugreifen und auch daran arbeiten.
Abb. 8: Freigegeben-Suffix der Excel-Datei
Nun prüfen wir, ob die Änderung an der Excel-Datei Auswirkungen auf unsere Job-Routine hat. Dazu starten wir den Job nochmals bei bereits geöffneter Excel-Datei. Das Ergebnis, das wir nun bei freigegebener Excel-Datei erhalten, ist umso erfreulicher: Der Job läuft erfolgreich durch, trotz bereits geöffneter Excel-Datei durch einen anderen Benutzer.
Abb. 9: Statusmeldung beim Ladeprozess mit freigegebener Excel-Datei
Durch die Freigabe entstehen im täglichen Datenladeprozess keine Fehler und Abbrüche mehr, die im Zusammenhang mit der Mehrbenutzerfähigkeit von Excel-Dateien stehen. Benutzer können gleichzeitig auch noch ohne Gefahr an den Excel-Dateien weiterarbeiten.
Arbeitsweisen mit freigegebenen Excel-Dateien
Was ändert sich nun in der täglichen Arbeitsweise mit Excel-Dateien, die für den Mehrbenutzerbetrieb freigeschaltet wurden? Durch die simultane Nutzung einer Excel-Datei entstehen die typischen Problemstellungen einer Mehrbenutzeranwendung. Was passiert z.B., wenn ein und dieselbe Zelle von zwei Benutzern geändert wird? Welche Änderung ist wirksam?
Hierfür hat Microsoft eine Änderungsnachverfolgung geschaffen, die sämtliche Änderungen mitprotokolliert. Damit können Änderungen anderer Benutzer gezielt angenommen oder abgelehnt werden. Die Einstellungsmöglichkeiten hierzu finden Sie ebenso unter dem Optionspunkt „Arbeitsmappe freigeben“. Im Reiter „Weitere“ kann gezielt eingestellt werden, wie die Änderungsnachverfolgung funktionieren soll. Es ist zu empfehlen, die Grundeinstellungen beizubehalten, da sie die Änderungsnachverfolgung schon optimal nutzt. Die Option zur Änderungsaktualisierung könnte jedoch in manchen Fällen sinnvoll sein. Hier kann entschieden werden, ob die Änderungen anderer Benutzer bereits beim Bearbeiten der Arbeitsmappe, nach einem einstellbaren Aktualisierungsintervall, oder erst beim Speichern der Datei angezeigt werden soll.
Abb. 10: Einstellungsmöglichkeiten der Arbeitsmappenfreigabe
Mit der Grundeinstellung funktioniert die Änderungsnachverfolgung dann folgendermaßen: Öffnen zwei Benutzer dieselbe Datei und führen Änderungen durch, so werden die Änderungen wirksam, welche zuerst gespeichert werden. Wenn der zweite Benutzer dann ebenso speichern will, wird ihm folgende Meldung angezeigt.
Abb. 11 Hinweis bei Änderungen
Gleichzeitig werden die betroffenen Zellen, die von anderen Benutzern geändert worden sind, mit einem blauen Rahmen und einem Kommentar kenntlich gemacht.
Abb. 12: Markierung von veränderten Zellen
Der Benutzer sieht damit die von einem anderen Benutzer durchgeführten Änderungen und kann sie entweder akzeptieren oder wiederum ablehnen und auf einen historischen Zustand zurückspringen. In den dafür zuständigen Dialog gelangen Sie über den Menüpunkt „Änderungen nachverfolgen“ -> „Änderungen annehmen/ablehnen“. Dort können Sie wiederum den Zeitraum, den Personenkreis, sowie den Bereich bestimmen, in dem die Änderungen vorgenommen wurden, die sie betrachten wollen. Im Anschluss können Sie schrittweise bestimmen welcher Wert für die jeweilige Zelle gültig sein soll.
Abb. 13: Dialogfeld mit Änderungshistorie
Sollten Änderungen von zwei oder mehreren Benutzern in derselben Zelle vorgenommen worden sein, öffnet sich beim Versuch des Speicherns der folgende Dialog, der wiederum zur Lösung des Konfliktes herangezogen wird. Hier kann bestimmt werden, welche Änderung der beiden Nutzer verwendet und schlussendlich abgespeichert werden soll.
Abb. 14: Dialogfeld zur Konfliktlösung
Einschränkungen
Auch wenn die freigegebene Excel-Datei sich für den reibungslosen Import deutlich besser eignet, als die normale Excel-Datei, ist die Freigabe mit Einschränkungen behaftet. Excel-Dateien, die über Tabellen-Elemente oder externe XML-Zuordnung verfügen, können erst gar nicht freigegeben werden. Hier erscheint der Warnhinweis, dass erst das Entfernen der XML-Zuordnung bzw. das Konvertieren in einen normalen Bereich der Tabelle notwendig ist, um diese Funktionalität zu nutzen.
Es bestehen aber auch nach der Freigabe der Arbeitsmappe Einschränkungen hinsichtlich des Funktionsumfangs. Beispielweise ist es nach der Umwandlung zwar möglich neue Arbeitsblätter hinzuzufügen, jedoch können keine mehr gelöscht werden. Auch neue bedingte Formatierungen oder das Einfügen von Bildern, neuen Diagrammen, Pivot-Tabellen oder SmartArt-Grafiken ist nicht mehr möglich.
Was sich erstmal sehr einschränkend und nach einem Ausschlusskriterium anhört, ist in der Praxis jedoch so gut wie nicht ausschlaggebend. Denn normalerweise benötigt eine Excel-Datei, die zum Datenimport herangezogen wird und rein über Bewegungs- oder Stammdaten verfügt, keine dieser Funktionalitäten, die mit der Freigabe ausgeschlossen werden. Somit ist die Freigabe in den meisten Fällen anwendbar und sorgt für den oben genannten Vorteil im Datenladeprozess.
Wenn widererwartend doch Funktionen benötigt werden, die nicht vom eingeschränkten Funktionsumfang abgedeckt werden, kann die Freigabe jederzeit wieder rückgängig gemacht werden, indem der Haken bei „Arbeitsmappe freigeben“ wieder herausgenommen und somit zur Grundeinstellung zurückgekehrt wird.
Anhang
Sollen mehrere Excel-Dateien gleichzeitig freigegeben werden und das Ganze soll ungern per Hand passieren, kann folgender VBA-Code genutzt werden, um sämtliche Excel-Dateien an dem angegebenen Pfad freizugeben. Dazu muss lediglich der Pfad im Code angepasst und der VBA-Code im Anschluss ausgeführt werden. (Sollten Sie xls.-Dateien haben, müssten Sie im Code noch “*.xlsx” durch “*.xls” ersetzen.)
Sub OpenWkb() Dim sFile As String, sPath As String Application.ScreenUpdating = False sPath = "Ihr_Pfad" If Right(sPath, 1) <> "/" Then sPath = sPath & "\" End If sFile = Dir(sPath & "*.xlsx") Do While sFile <> "" Workbooks.Open sPath & sFile sFile = Dir() ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _ accessMode:=xlShared Application.DisplayAlerts = False ActiveWorkbook.Close Loop Application.ScreenUpdating = True End Sub
QUELLE: Code teilweise von herber.de entnommen.
