Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer
Gilt für:
SQL Server SSIS Integration Runtime in Azure Data Factory
In diesem Thema wird beschrieben, wie mithilfe des Foreach-Schleifencontainers und dem entsprechenden Enumerator die Excel-Arbeitsmappen in einem Ordner oder die Tabellen in einer Excel-Arbeitsmappe durchlaufen werden.
Wichtig
Ausführliche Informationen über das Herstellen einer Verbindung mit Excel-Dateien sowie Einschränkungen und bekannte Probleme beim Laden von Daten aus oder in Excel-Dateien finden Sie unter Load data from or to Excel with SQL Server Integration Services (SSIS) (Laden von Daten aus oder in Excel mit SQL Server Integration Services (SSIS)).
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 dieser Prozedur gezeigten Beispielausdruck wird der Variablenname
ExcelFileverwendet.)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 dieser Prozedur dargestellt wird, verwendet den Variablennamen
ExtPropertiesund weist einen Anfangswert von "Excel 12.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 eines Foreach-Schleifencontainers finden Sie unter Konfigurieren eines Foreach-Schleifencontainers.
Wählen Sie auf der Seite Sammlung 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 *.xlsx).
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 dieser Prozedur gezeigten Beispielausdruck wird der Variablenname
ExcelFileverwendet.)Schließen Sie den Foreach-Schleifen-Editor.
Fügen Sie dem Paket einen Excel-Verbindungs-Manager hinzu, wie unter Hinzufügen, Löschen oder Freigeben eines Verbindungs-Managers in einem Paketbeschrieben. 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 die Verbindungszeichenfolgen-Eigenschaft des Excel-Verbindungs-Managers allerdings erst dann wieder gültig, wenn die Foreach-Schleife ausgeführt wird. 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.
Sie müssen auch den Standardwert von False für die RetainSameConnection -Eigenschaft des Excel-Verbindungs-Managers verwenden. Wenn Sie diesen Wert auf True ändern, wird jede Iteration der Schleife weiterhin die erste Excel-Arbeitsmappe öffnen.
Wählen Sie den neuen Excel-Verbindungs-Manager aus, klicken Sie im Eigenschaftenfenster auf die Eigenschaft Ausdrücke , 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.ACE.OLEDB.12.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, wie im folgenden Beispiel gezeigt, manuell einen Wert hinzufügen:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties=Excel 12.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 mit dem Microsoft ACE OLE DB-Anbieter eine Verbindung mit einer Excel-Arbeitsmappe herstellt. Stellen Sie auf der Seite „Alle“ des Dialogfelds Verbindungs-Manager sicher, dass Sie die Excel-Version (12.0 in diesem Fall) als Wert der Eigenschaft „Erweiterte Eigenschaften“ eingeben. Weitere Informationen finden Sie unter Hinzufügen, Löschen oder Freigeben 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 eines Foreach-Schleifencontainers finden Sie unter 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.
Siehe auch
Laden von Daten aus oder in Excel mit SQL Server Integration Services (SSIS)
Konfigurieren eines Foreach-Schleifencontainers
Hinzufügen oder Ändern eines Eigenschaftsausdrucks
Excel-Verbindungs-Manager
Excel-Quelle
Excel-Ziel
Arbeiten mit Excel-Dateien mit dem Skripttask
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für