Tabellenblätter einer Excel Datei lassen sich ohne weiteres mit Hilfe des Excel Quellen-Editors in SSIS abfragen. Kürzlich stand ich jedoch vor der Herausforderung, dass sich je nach Version der zu ladenden Excel Datei die Quelldaten in anderen Zellen befinden. Ich bin zunächst davon ausgegangen, dass für diese Anforderung die Skriptkomponente verwendet werden muss. Eine Suche in den Tiefen des Internets hat mich eines Besseren belehrt. In diesem Blogbeitrag wird das Erstellen von Excel Abfragen mit verändernden Zellbezügen und das Abfragen einzelner Zellwerte erklärt.
Als Beispieldatensätze werden auch dieses Mal die Verkaufszahlen des großen Fahrradherstellers Adventure Works Cycles herangezogen.
Nachdem wir ein neues Projekt mit einem Datenflusstask angelegt haben, fügen wir diesem eine Excel Quelle hinzu. Für mich bereits neu war an dieser Stelle, dass man dort auch SQL Statements (auf Excel angepasst) eintragen kann die nur bestimmte Zellen abfragen können. Bisher bin ich immer davon ausgegangen, dass man nur den Inhalt der ganzen Spalte abfragen kann. Wie das möglich ist, zeigt Abbildung 1. Zu beachten ist, dass der richtige Name des Tabellenblatts verwendet wird.
Es ist an dieser Stelle nicht notwendig, die Spalte über die Angabe F1 für die Auswahl von Zelle B2 in F2 zu ändern. Dies führt vielmehr zu einem Absturz des Visual Studio wenn man auf Vorschau klickt.
Hinter weiteren gelegentlichen Abstürzen während der Bearbeitung des SQL Befehls vermutete ich zunächst ein falsches SQL Statement. Ein Wechsel auf den 32-Bit Modus in den Einstellungen des Projekts (siehe Abbildung 2) zeigte aber, dass das Problem damit behoben werden konnte.
Als Grundlage für die Zellbezüge dient mir eine im SQL Server hinterlegte Mapping Tabelle. Den Inhalt der abzufragenden Zellen zeigt Abbildung 3.
Mit steigender Regionsanzahl und/oder abzufragender Zellen nimmt der Pflegeaufwand für die Mapping Tabelle natürlich drastisch zu.
Für die erfolgreiche Umsetzung müssen in SSIS außerdem folgende Variablen (Datentypen beachten) angelegt werden:
Die eingetragenen Werte dienen lediglich dem Erzeugen eines validen SQL Statements. Die Werte der Variablen liefert eine SQL Abfrage (dazu später).
Zunächst muss in den Eigenschaften für die Variable sExcelStatement der Wert für EvaluateAsExpression von False auf True (Abbildung 5) geändert werden.
Danach kann die Expression bearbeitet werden.
Code:
Im Excel Quellen-Editor ändern wir den Datenzugriffsmodus auf SQL-Befehl aus Variable und wählen unsere Variable sExcelStatement aus (Abbildung 7).
Ein Klick auf Vorschau sollte an dieser Stelle bereits die ersten Zellinhalte zurückgeben.
Abbildung 8 zeigt die bereits fertige Ablaufsteuerung. Jeder der das Tutorial mitgeklickt hat, sollte bisher nur den Datenflusstask (Schritt 4) sehen.
Schritt 1:
Wir beginnen damit einen Foreach-Schleifencontainer in die Ablaufsteuerung zu ziehen. Dessen Konfiguration zeigen Abbildung 9 und Abbildung 10.
Es ist darauf zu achten, dass in der Auflistung Nur Name ausgewählt wird. Hier sind natürlich auch andere Auswahlmöglichkeiten denkbar. Der Einfachheit halber habe ich mich für diesen Beitrag für einen Vergleich von Dateinamen und dem in der Mapping Tabelle hinterlegten Versionsnamen entschieden.
Schritt 2:
Um einen Teil der angelegten Variablen mit Werten zu füllen, habe ich im Task SQL ausführen folgende SQL Abfrage angelegt:
SELECT [id],[version],[modelname1],[internetsalescount1],[modelname2],[internetsalescount2],
[modelname3],
[internetsalescount3]
FROM [dbo].[mappinginternetsalescount]
WHERE [version] = ?
Die ResultSet-Eigenschaften müssen auf Vollständiges Resultset gestellt werden.
Während in der Parameterzuordnung die Variable sDateiname (wurde zuvor in Schritt 1 gefüllt) wird im Resultset die Variable rsMappingExcel ausgewählt.
Danach verbinden wir den SQL ausführen Task mit dem nächsten Foreach-Schleifencontainer.
Schritt 3:
Im zweiten Foreach-Schleifencontainer (siehe Abbildung 14) wählen wir als Enumerator den Foreach-ADO-Enumerator um über die zuvor gefüllte Variable rsMappingExcel zu iterieren.
Unter der Registerkarte Variablenzuordnungen wird das Ergebnis des unter Schritt 2 ausgeführten SQL Statements den einzelnen Variablen zugeordnet.
Etwaige Änderungen am SQL Statement und der Reihenfolge der Attribute müssen natürlich bei der Vergabe des Index angepasst werden.
Schritt 4:
Da der Datenflusstask bereits vorhanden ist muss im vierten Schritt nur noch der Verbindungs-Manager für die Excel Verbindung angepasst werden. Mit einem Klick auf den rot markierten Button (Abbildung 16) öffnet sich der Eigenschaftsausdrucks-Editor (Abbildung 17). Als Eigenschaft ist ExcelFilePath auszuwählen.
Der Ausdruck ist natürlich auf den eigenen Pfad anzupassen. Hier nur exemplarisch wie es bei mir aussieht (Escapesequenz für \ beachten).
Zum Beweis abschließend natürlich noch der Durchlauf des Pakets. Ich habe dafür zwei Dateien (Region1.xlsx und Region2.xlsx) abgelegt. Die Daten dazu zeigt Abbildung 19.
Ich habe den Block A10:B13 aus der Originaldatei Region1.xlsx in einer zweiten zu ladenden Datei Region2.xlsx gespeichert.
Der Daten-Viewer im Datenfluss (Schritt 4) liefert für die zwei Dateien mit den unterschiedlichen Zellbezügen die gleichen Ergebnisse (Abbildung 20 und Abbildung 21).
Abschließend noch die verwendeten Dateien:
Anregungen, Fragen und Kritik dann wieder immer gern über die Kommentarfunktion.