Zum Aufbau eines Data Warehouse (DWH) werden sehr oft die Daten aus dem ERP- oder CRM-System als sogenannte Flatfiles (Textfiles) oder auch als Exceldateien zur Verfügung gestellt. Ein typischer Fall könnte etwa eine Exceldatei mit den Budgetdaten aller Tochtergesellschaften für das nächste Jahr sein.

Alle Länder liefern dabei eine Exceldatei mit ihren jeweiligen Plandaten. Im Headquarter werden diese Exceldateien zentral in einer einzigen Excel-Arbeitsmappe zusammengeführt. Die Aufgabenstellung lautet nun, unabhängig von der Anzahl der Arbeitsblätter, also Länder, und anderen Merkmalen, wie etwa Vertriebskanal oder Vertriebsregion, einen dynamischen Importprozess zu implementieren.

In diesem Blogbeitrag stellen wir Ihnen mit Hilfe von SQL Server Integration Services (SSIS) und darin erstellten Integration Services Package vor, wie man solch eine Anforderung realisieren kann.

Aufbau der Excel-Arbeitsmappe

Zur Veranschaulichung nutzen wir eine Exceldatei mit Jahres-Plandaten. Jedes Arbeitsblatt hat folgenden Aufbau:

Die Plandaten werden für ein Jahr erfasst, somit entspricht die Spalte „Stück“ die geplanten Jahresstückzahlen. Die Exceldatei wird um die Merkmale „Vertriebsregion“ und „Vertriebskanal“ erweitert. Allerdings möchte man ungern bei existierenden 60 – 80 Tochtergesellschaften alle Excelblätter um die obigen Merkmale ergänzen. Dazu fehlt auch noch das „Planjahr“ also das Zeitmerkmal.

Empfehlungen bei der Anpassung der Excel-Arbeitsmappe

Um die Arbeit für die Mitarbeiter so gering wie möglich zu halten, wird empfohlen, die Arbeitsblätter etwa folgendermaßen zu benennen: „Plan_Distribution_Süd“ oder „Plan_Shop_Nord“

Somit werden die Merkmale „Vertriebskanal“ (Distribution, Shop, etc.) und „Vertriebsregion“ (Nord, Süd…) in den Blattnamen verankert und durch z. B. „Unterstrich“ oder andere Zeichen voneinander getrennt.

Man könnte zwar das Budgetjahr (2011) auch in den Blattnamen unterbringen, hätte aber dann den Nachteil im darauffolgenden Jahr alle Arbeitsblätternamen wieder anpassen zu müssen. Aus diesem Grund vergeben wir das Planjahr in dem Dateinamen, etwa so: „Plandaten_2011.xls“.

Importieren der Exceldatei in SQL Server

Beim Import der Exceldatei in SQL Server werden wir nicht, wie üblich, alle Tabellenblätter selektieren und importieren, sondern nur eines der Excelblätter.

Das Ziel ist also nicht eine SQL-Tabelle pro Excelblatt zu erzeugen, sondern mit Hilfe von dem SSIS-Paket und Variablen über alle vorhandenen Arbeitsblätter zu iterieren, nur eine SQL-Tabelle zu erstellen und somit dynamisch auf neue oder nicht mehr vorhandene Arbeitsblätter zu reagieren.

Die Liste der Tabellen in einer Excel-Arbeitsmappe schließt sowohl Arbeitsmappen (diese weisen das Suffix $ auf) als auch benannte Bereiche ein. (Suffix $Druckbereich etc.)

Zu einem späteren Zeitpunkt müssen wir die Liste, in diesem Fall, nach nur Arbeitsmappen filtern, und zu diesem Zweck möglicherweise einen benutzerdefinierten Code in einem Skripttask schreiben.

Im letzten Schritt des Importassistenten speichern wir das SSIS-Paket, um es später anzupassen.

Nach erfolgreichem Import wird die Tabelle im SQL Server Management Studio um zwei weitere Spalten vom Typ „Varchar“ erweitert. Die Spalte „FileName“ ist für den Dateinamen vorgesehen. Die Spalte „SheetName“ soll jeden Tabellennamen aufnehmen.

Implementierungen im SSIS-Paket

Nun öffnen wir das zuvor gespeicherte SSIS-Paket (.dtsx) im Visual Studio und legen zuerst auf Paketebene drei Variablen an:

• varFileName vom Typ String
• varSheets vom Typ Objekt
• varSheetName vom Typ String

und belegen sie wie folgt:

• varFileName mit dem Pfad und Dateinamen der zu importierenden Datei
• varSheets wird automatisch als „System.Object“ belegt.
• varSheetName mit dem Namen des ersten Tabellenblattes

Das angelegte SSIS-Paket beinhaltet in der Registerkarte „Ablaufsteuerung“ zwei Tasks. Wir erweitern den „Datenflusstask“ nun sukzessive.

Zuerst fügen wir aus dem Toolbox das Ablaufsteuerungselement „Foreach-Schleifencontainer“ in die Ablaufsteuerung hinzu.

Wir bearbeiten den Container (Kontextmenü – Bearbeiten) wie folgt:

Im Bereich „Auflistung“ wird der Enumerator „Foreach-Datei-Enumerator“ ausgewählt. In der Enumeratorkonfiguration stellt man den Pfad zum Ordner der Zieldatei ein.

Im Bereich „Variablenzuordnungen“ nutzen wir die zuvor angelegte Variable „varFileName“ mit dem Index 0.

Jetzt fügen wir noch aus der Toolbox das Ablaufsteuerungselement „Skripttask“ in die Ablaufsteuerung hinzu.

Das Ziel ist nun in dem Skripttask, aus jeder vorhandenen Exceldatei im Verzeichnis, ein sogenanntes „Array“ also ein Ansammlung ihrer Excelblätter zu erzeugen. Dabei interessieren uns nur die Excelblätter mit dem Suffix $.

Dafür klicken wir doppelt auf dem Skripttask und öffnen somit den Editor, stellen im Bereich „Skript“ bei „EntryPoint“ den Wert „Main“ ein, wenn nicht schon vorbelegt. Wir wählen bei „ReadOnlyVariables“ die Variable „varFileName“ und bei „ReadWriteVariables“ die Variable „varSheets“ aus. Als Input soll der varFileName gelesen und ein Array erstellt werden. Der Inhalt des Arrays soll in die Variable varSheets geschrieben werden.

Jetzt klicken wir zum Öffnen des Skript-Editors auf „Skript bearbeiten…“ weiter unten.
Und fügen den folgenden Code hinzu. Es kann sein, dass Teile des Codes schon standardmäßig vorhanden sind.

Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum




Public Sub Main()
Dim excelFile As String
Dim connectionString As String
Dim excelConnection As OleDbConnection
Dim tablesInFile As DataTable
Dim tableCount As Integer = 0
Dim tableInFile As DataRow
Dim currentTable As String
Dim tableIndex As Integer = 0

Dim excelTables As String()

excelFile = Dts.Variables("varFileName").Value.ToString
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & excelFile & _
";Extended Properties=Excel 8.0"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
tablesInFile = excelConnection.GetSchema("Tables")
tableCount = tablesInFile.Rows.Count
ReDim excelTables(tableCount - 1)
For Each tableInFile In tablesInFile.Rows
If Right(tableInFile.Item("TABLE_NAME").ToString, 1) = "$" Then
currentTable = tableInFile.Item("TABLE_NAME").ToString
excelTables(tableIndex) = currentTable
tableIndex += 1
End If
Next

ReDim Preserve excelTables(tableIndex - 1)

Dts.Variables("varSheets").Value = excelTables

Dts.TaskResult = ScriptResults.Success
End Sub

End Class

Als letztes Ablaufsteuerungselement fügen wir noch ein „Foreach-Schleifencontainer“ aus der Toolbox in die Ablaufsteuerung hinzu und wählen als Enumerator im Bereich „Auflistung“ den Eintrag „Foreach-Enumerator für Daten aus Variable“ aus. Weiter unten für „Enumeratorkonfiguration“ wird die Variable „varSheets“ selektiert.

Im Bereich „Variablenzuordnungen“ ist die Auswahl die Variable „varSheetName“ mit dem Index 0.

In der Registerkarte „Datenfluss“ erweitern wir die Datenflusstasks mit dem Task „Abgeleitete Spalte“ aus der Toolbox und dem Bereich „Datenflusstransformationen“.

Mit dieser Methode aktualisieren wir die zuvor angelegten Spalten in der Importtabelle mit Ausdrücken.

Mit Doppelklick öffnen wir den Editor und legen etwa wie in der Abbildung die Ausdrücke fest, um die Inhalte der Variablen „FileName“ und „SheetName“ zurecht zu schneiden und in die jeweiligen Spalten zu schreiben.

Jetzt wird der Task „Abgeleitete Spalte“ zwischen der Quelle und dem Ziel platziert und mit den Verbindungspfeilen versehen.

Es muss jetzt nachträglich für die korrekte Zuordnung zwischen den neu definierten Ausdrucksspalten und den neuen Spalten in der Importtabelle gesorgt werden. Dies passiert in dem Ziel-Task im Bereich „Zuordnungen“.

Bei dem Quell-Task muss noch folgende Änderungen vorgenommen werden. Im Quellen-Editor (Doppelklick auf Task) wird im Bereich „Verbindungs-Manager“ bei „Datenzugriffsmodus“ der aktuelle Eintrag „Tabelle oder Sicht“ durch „Variable für Tabellenname oder Sichtname“ ersetzt. Automatisch ändert sich der untere Eintrag in „Variablenname“. Hier muss die Variable „varSheetName“ ausgewählt werden.

Nun kehren wir zur „Ablaufsteuerung“ zurück. Dort existieren jetzt 5 Tasks, die wir zusammen bringen müssen. Die folgende Abbildung zeigt, wie die Tasks ineinander geschachtelt werden.

Die äußere Schleife liest jede existierende Datei im Pfad. Der Skripttask „GetExcelTables“ sorgt dafür, dass nur die Tabellenblätter mit Suffix $ als Array definiert und in die Variable „varSheets“ geschrieben werden.
Die innere Schleife öffnet Blatt für Blatt und ruft den Datenflusstask auf, liest die Werte und überträgt sie in die Importtabelle.

Besonderheiten

Die Excel-Tabellenblatt-Namen dürfen keinerlei Sonderzeichen oder Leerzeichen enthalten, nur Buchstaben, Zahlen und Unterstrich funktionieren. Andernfalls gibt es leider auch keinen Abbruch oder eine Fehlermeldung. Der Import der entsprechenden Blätter wird einfach stillschweigend nicht ausgeführt und ignoriert.