Vorgehensweise: Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer
Die Verfahren in diesem Thema beschreiben, wie mithilfe des Foreach-Schleifencontainers mit dem entsprechenden Enumerator die Excel-Arbeitsmappen in einem Ordner oder die Tabellen in einer Excel-Arbeitsmappe durchlaufen werden.
So durchlaufen Sie Excel-Dateien mithilfe des Foreach-Dateienumerators
Erstellen Sie eine Zeichenfolgenvariable, die den aktuellen Excel-Pfad und -Dateinamen auf jeder Iteration der Schleife empfängt. Zur Vermeidung von Validierungsproblemen weisen Sie einen gültigen Excel-Pfad und einen Dateinamen als Anfangswert der Variablen zu. (Im weiter unten in diesem Verfahren gezeigten Beispielausdruck wird der Variablenname ExcelFile verwendet.)
Erstellen Sie optional eine weitere Zeichenfolgenvariable für den Wert des Arguments Erweiterte Eigenschaften der Excel-Verbindungszeichenfolge. Dieses Argument enthält eine Reihe von Werten, die die Excel-Version angeben und bestimmen, ob die erste Zeile Spaltennamen enthält und ob der Importmodus verwendet wird. (Der Beispielausdruck, der weiter unten in diesem Verfahren dargestellt wird, verwendet den Variablennamen ExtProperties und weist einen Anfangswert von "Excel 8.0;HDR=Yes" auf.)
Wenn Sie keine Variable für das Argument "Erweiterte Eigenschaften" verwenden, müssen Sie es dem Ausdruck, der die Verbindungszeichenfolge enthält, manuell hinzufügen.
Fügen Sie der Registerkarte Ablaufsteuerung einen Foreach-Schleifencontainer hinzu. Informationen zum Konfigurieren des Foreach-Schleifencontainers finden Sie unter Vorgehensweise: Konfigurieren eines Foreach-Schleifencontainers.
Wählen Sie auf der Seite Auflistung des Foreach-Schleifen-Editors den Foreach-Dateienumerator aus, geben Sie den Ordner an, in dem sich die Excel-Arbeitsmappen befinden, und geben Sie den Dateifilter an (normalerweise *.xls).
Ordnen Sie auf der Seite Variablenzuordnungen Index 0 einer benutzerdefinierten Zeichenfolgenvariablen zu, die bei jeder Iteration der Schleife den aktuellen Excel-Pfad und Dateinamen empfangen wird. (Im weiter unten in diesem Verfahren gezeigten Beispielausdruck wird der Variablenname ExcelFile verwendet.)
Schließen Sie den Foreach-Schleifen-Editor.
Fügen Sie dem Paket einen Excel-Verbindungs-Manager hinzu wie unter Vorgehensweise: Hinzufügen oder Löschen eines Verbindungs-Managers in einem Paket beschrieben. Wählen Sie für die Verbindung eine vorhandene Excel-Arbeitsmappendatei aus, um Überprüfungsfehler zu vermeiden.
Wichtig Wählen Sie im Verbindungs-Manager-Editor für Excel eine vorhandene Excel-Arbeitsmappe aus, um Überprüfungsfehler zu vermeiden, wenn Sie Tasks und Datenflusskomponenten konfigurieren, die diesen Excel-Verbindungs-Manager verwenden. Diese Arbeitsmappe wird vom Verbindungs-Manager zur Laufzeit nicht mehr verwendet, wenn Sie, wie in den folgenden Schritten beschrieben, einen Ausdruck für die ConnectionString-Eigenschaft konfigurieren. Nachdem Sie das Paket erstellt und konfiguriert haben, können Sie im Eigenschaftenfenster den Wert der ConnectionString-Eigenschaft löschen. Wenn Sie diesen Wert löschen, ist jedoch die Eigenschaft der Verbindungszeichenfolge des Excel-Verbindungs-Managers erst wieder gültig, nachdem die Foreach-Schleife ausgeführt wurde. Daher müssen Sie für die Tasks, in denen der Verbindungs-Manager verwendet wird, oder für das Paket die DelayValidation-Eigenschaft auf True festlegen, um Überprüfungsfehler zu vermeiden.
Zusätzlich müssen Sie den Standardwert False für die RetainSameConnection-Eigenschaft des Excel-Verbindungs-Managers angeben. Wenn Sie diesen Wert in True ändern, wird bei jeder Iteration der Schleife wieder die erste Excel-Arbeitsmappe geöffnet.
Wählen Sie den neuen Excel-Verbindungs-Manager aus, klicken Sie im Eigenschaftenfenster auf die Ausdrücke-Eigenschaft, und klicken Sie anschließend auf die Auslassungspunkte.
Wählen Sie im Eigenschaftsausdrucks-Editor die ConnectionString-Eigenschaft aus, und klicken Sie anschließend auf die Auslassungspunkte.
Geben Sie im Ausdrucks-Generator den folgenden Ausdruck ein:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
Beachten Sie die Verwendung des Escapezeichens "\" bei den inneren Anführungszeichen, die um den Wert des Arguments für erweiterte Eigenschaften herum erforderlich sind.
Das Argument "Erweiterte Eigenschaften" ist nicht optional. Wenn Sie keine Variable zur Angabe des Werts verwenden, müssen Sie dem Ausdruck manuell einen Wert hinzufügen, wie im folgenden Beispiel einer Excel 2003-Datei gezeigt:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties=Excel 8.0"
Erstellen Sie Tasks innerhalb des Foreach-Schleifencontainers, die den Excel-Verbindungs-Manager verwenden, um für alle Excel-Arbeitsmappen, die dem angegebenen Dateispeicherort und Dateimuster entsprechen, die gleichen Vorgänge auszuführen.
So durchlaufen Sie Excel-Tabellen mithilfe des Enumerators für Foreach-ADO.NET-Schemarowset
Erstellen Sie einen ADO.NET-Verbindungs-Manager, der den Microsoft Jet OLE DB-Anbieter zum Herstellen einer Verbindung mit einer Excel-Arbeitsmappe verwendet. Stellen Sie auf der Seite Alle des Dialogfelds Verbindungs-Manager sicher, dass Sie Excel 8.0 als Wert der Eigenschaft für erweiterte Eigenschaften eingeben. Weitere Informationen finden Sie unter Vorgehensweise: Hinzufügen oder Löschen eines Verbindungs-Managers in einem Paket.
Erstellen Sie eine Zeichenfolgenvariable, die bei jeder Iteration der Schleife den Namen der aktuellen Tabelle empfangen wird.
Fügen Sie der Registerkarte Ablaufsteuerung einen Foreach-Schleifencontainer hinzu. Informationen zum Konfigurieren des Foreach-Schleifencontainers finden Sie unter Vorgehensweise: Konfigurieren eines Foreach-Schleifencontainers.
Wählen Sie auf der Seite Auflistung des Foreach-Schleifen-Editors den Enumerator für Foreach-ADO.NET-Schemarowset aus.
Wählen Sie als Wert für Verbindung den ADO.NET-Verbindungs-Manager aus, den Sie zuvor erstellt haben.
Wählen Sie als Wert für Schema die Option Tabellen aus.
Hinweis Die Liste der Tabellen in einer Excel-Arbeitsmappe schließt sowohl Arbeitsmappen (diese weisen das Suffix $ auf) als auch benannte Bereiche ein. Wenn Sie die Liste nach nur Arbeitsmappen oder nach nur benannten Bereichen filtern müssen, müssen Sie zu diesem Zweck möglicherweise benutzerdefinierten Code in einem Skripttask schreiben. Weitere Informationen finden Sie unter Arbeiten mit Excel-Dateien mit dem Skripttask.
Ordnen Sie auf der Seite Variablenzuordnung den Index 2 der zuvor erstellten Zeichenfolgenvariablen zu, die den Namen der aktuellen Tabelle enthält.
Schließen Sie den Foreach-Schleifen-Editor.
Erstellen Sie Tasks innerhalb des Foreach-Schleifencontainers, die den Excel-Verbindungs-Manager verwenden, um für alle Excel-Tabellen in der angegebenen Arbeitsmappe die gleichen Vorgänge auszuführen. Wenn Sie einen Skripttask zum Überprüfen der aufgezählten Tabellennamen oder zum Arbeiten mit den Tabellen verwenden, müssen Sie die Zeichenfolgenvariable der ReadOnlyVariables-Eigenschaft des Skripttasks hinzufügen.