Zum Import von Daten aus Microsoft Excel in SQL Server via Integration Services (SSIS) sind spezielle Treiber erforderlich – welche genau, ist abhängig von diversen Faktoren. Was dabei im Detail zu beachten ist, fasst dieser Blogbeitrag zusammen.
In vielen BI-Projekten werden die relevanten Rohdaten nicht nur direkt aus den Datenbanken der operativen Vorsysteme extrahiert, sondern manuell oder semiautomatisch in Dateiform bereitgestellt. Das gebräuchlichste Format ist dabei Microsoft Excel.
Üblicherweise kommt beim Extraktionsprozess das SQL-Server-Modul Integration Services (SSIS) zum Einsatz. Dessen Lieferumfang beinhaltet im Standard zwar diverse Anbieter (Treiber) zum Zugriff auf verschiedene Quellformate, jedoch nicht die erforderlichen Komponenten für aktuelle Excel-Versionen (Dateiendung .xlsx). Diese müssen manuell nachinstalliert werden.
Hier lauert die erste Verwechslungsgefahr: An einigen Stellen in SSIS taucht der Begriff Excel zwar auch ohne separat installierte Treiber auf, nach der Auswahl stellt sich jedoch heraus, dass die Bordmittel nur den Zugriff auf das alte Excel-Format bis Office 2003 (Dateiendung .xls) erlauben. Dahinter verbirgt sich der sogenannte JET-Provider, der von Microsoft über viele Jahre als Standard zum Zugriff sowohl auf Access als auch Excel ausgeliefert wurde.
Nicht nur interessant zu wissen, sondern wichtig zum Verständnis so mancher Details zum Verhalten von SSIS in Kombination mit Excel-Daten: Excel wird vom Treiber wie eine Datenbank behandelt. Die Datei entspricht der Datenbank, die Arbeitsblätter sowie benannten Bereiche (Ranges) den enthaltenen Tabellen bzw. Sichten. Die oberste Zeile bildet jeweils die Spaltenüberschriften und der Inhalt der Spalten wird zur Interpretation der Datentypen verwendet.
Wie genau sich SSIS hierbei verhält, ist versionsabhängig. Das daraus resultierende Fehlerpotential mit Tipps für entsprechende Workarounds füllen ganze Bücher und Webforen. An dieser Stelle sei zum Grundverständnis so viel bemerkt: Die erste Datenzeile sollte idealerweise realistische Daten enthalten. Leere Zellen sollten in der ersten Zeile vermieden werden. Dies sowie gemischte numerische und alphanumerische Inhalte in den Zeilen können beim Import zu Fehlern führen.
Der JET-Provider wurde nur als 32bit-Komponente entwickelt. Sofern er zum Einsatz kommen soll, ist folgendes zu beachten:
– Bei der Ausführung des SSIS-Pakets aus Data Tools (SSDT-BI) ist in den Projekteigenschaften im Bereich „Debugging“ die Option „Run64bitRuntime“ auf False zu setzen.
– Beim automatisierten Aufruf als Auftrag (Job) in SQL Server Agent ist in den Eigenschaften des Schritts auf der Registerkarte „Ausführungsoptionen“ die Option „32-bit-Laufzeitumgebung verwenden“ anzukreuzen – also genau die umgekehrte Logik wie in der Entwicklungsumgebung!
– Das SSIS-Paket selbst muss nicht verändert werden. Die beiden obigen Optionen sind Parameter, die bei der Ausführung des Pakets angewendet werden.
Zum Zugriff auf XLSX-Dateien (ab Office 2007) ist die „Access Database Engine“ (oft kurz als ACE-Provider bezeichnet) erforderlich. Diese existiert in verschiedenen Versionen (aktuell von 2010 bis 2016 bzw. 12.0 bis 16.0), wobei uns keine Kompatibilitätsprobleme im Zusammenspiel mit unterschiedlichen Versionen von SQL Server bzw. Visual Studio bekannt sind.
Den ACE-Provider gibt es darüber hinaus jedoch in der 32bit- und 64bit-Variante. Und jetzt wird es interessant: Bei der Installation überprüft das MSI-Paket die Existenz weiterer Office-Komponenten, und es gilt die Spielregel: Sämtliche auf dem betroffenen Rechner installierten Komponenten müssen von derselben „Bittigkeit“ sein. Dies kann eine unvorteilhafte Abhängigkeitskette auslösen: Wenn ein Unternehmen beispielsweise aus Gründen der Abwärtskompatibilität einzelner Anwendungen Office generell in der 32bit-Variante einsetzt, muss auch der 32bit-ACE-Provider verwendet werden. Microsoft Visual Studio (und damit Data Tools for BI) ist generell eine 32bit-Software. Mit Hilfe der oben zum JET-Provider beschriebenen Schalter ist hier aber Abhilfe möglich. Soll jedoch beispielsweise auch mit DeltaMaster im Self-Service-Modus (Option „MDX Analytics“) auf XLSX-Dateien zugegriffen werden, ist auch eine 32bit-Installation von DeltaMaster erforderlich, da nur diese mit dem 32-bit-ACE-Provider kommunizieren kann…
Weiter ist in verteilten Umgebungen zu beachten, dass der gewünschte Provider auf allen betroffenen Maschinen installiert werden muss, also z.B. sowohl auf dem Application Server (auf dem in der Regel SSDT-BI zur Entwicklung eingesetzt wird) als auch auf dem Datenbankserver (auf dem SQL Server Agent als Dienst läuft).
Die gute Nachricht zum Schluss: Entgegen der Aussagen in vielen Foren ist durchaus eine parallele Installation des ACE-Providers in der 32bit- und der 64bit-Variante möglich. Dies spart insbesondere dem BC-BI-Berater oder Business Partner die doppelte Installation und Aktualisierung aller DeltaMaster-Komponenten. Allerdings darf die Installation nicht im Dialogmodus per Doppelklick auf das MSI-Paket erfolgen, sondern über die Kommandozeile (cmd.exe) und unter Verwendung des Parameters „/passive“ bzw. „/quiet“ je nach der eingesetzten Office-Version.
Hier der Servicelink:
https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/
Wir hoffen, mit dieser Zusammenfassung etwas Licht ins Dunkel des SSIS-Excel-JET-ACE-Dschungels gebracht zu haben. Viel Erfolg bei der Anwendung!
