Arbeiten mit Excel-Dateien mit dem Skripttask

Integration Services stellt den Excel-Verbindungs-Manager, die Excel-Quelle und das Excel-Ziel zum Arbeiten mit den in Kalkulationstabellen gespeicherten Daten im Microsoft Excel-Dateiformat bereit. Die in diesem Thema beschriebenen Verfahren verwenden den Skripttask zum Abrufen von Informationen über verfügbare Excel-Datenbanken (Arbeitsmappendateien) und -Tabellen (Arbeitsmappen und benannte Bereiche). Diese Beispiele können ganz einfach geändert werden, um mit einer der anderen dateibasierten Datenquellen zu arbeiten, die vom Microsoft Jet OLE DB-Anbieter unterstützt werden.

Konfigurieren eines Pakets zum Testen der Beispiele

Beispiel 1: Überprüfen, ob eine Excel-Datei vorhanden ist

Beispiel 2: Überprüfen, ob eine Excel-Tabelle vorhanden ist

Beispiel 3: Abrufen einer Liste der Excel-Dateien in einem Ordner

Beispiel 4: Abrufen einer Liste der Tabellen in einer Excel-Datei

Anzeigen der Ergebnisse dieser Beispiele

Konfigurieren eines Pakets zum Testen der Beispiele

Sie können ein einzelnes Paket konfigurieren, um alle Beispiele in diesem Thema zu testen. In den Beispielen werden oft die gleichen Paketvariablen und die gleichen .NET Framework-Klassen verwendet.

So konfigurieren Sie ein Paket zur Verwendung mit den in diesem Thema beschriebenen Beispielen

1. Erstellen Sie in Integration Services ein neues SQL Server Data Tools (SSDT)-Projekt, und öffnen Sie das Standardpaket für die Bearbeitung.

2. Variablen: Öffnen Sie das Fenster Variablen, und definieren Sie die folgenden Variablen:

   • ExcelFile vom Typ String. Geben Sie den vollständigen Pfad zu einer vorhandenen Excel-Arbeitsmappe und den zugehörigen Dateinamen ein.

   • ExcelTable vom Typ String. Geben Sie den Namen eines vorhandenen Arbeitsblatts oder eines benannten Bereichs in der Arbeitsmappe ein, der im Wert der ExcelFile-Variablen genannt wird. Bei diesem Wert wird die Groß-/Kleinschreibung beachtet.

   • ExcelFileExists vom Typ Boolean.

   • ExcelTableExists vom Typ Boolean.

   • ExcelFolder vom Typ String. Geben Sie den vollständigen Pfad eines Ordners ein, der mindestens eine Excel-Arbeitsmappe enthält.

   • ExcelFiles vom Typ Object.

   • ExcelTables vom Typ Object.

3. Imports-Anweisungen. Für die meisten Codebeispiele müssen am Anfang der Skriptdatei einer oder beide der folgenden .NET Framework-Namespaces importiert werden:

   • System.IO für Dateisystemvorgänge.

   • System.Data.OleDb zum Öffnen von Excel-Dateien als Datenquellen.

4. Verweise. Für die Codebeispiele, die Schemainformationen in Excel-Dateien lesen, ist ein zusätzlicher Verweis im Skriptprojekt für den System.Xml-Namespace erforderlich.

5. Verwenden Sie im Dialogfeld Optionen auf der Seite Allgemein die Option Skriptsprache, um die Standardskriptsprache für die Skriptkomponente festzulegen. Weitere Informationen finden Sie unter General Page.

Beschreibung zu Beispiel 1: Überprüfen, ob eine Excel-Datei vorhanden ist

In diesem Beispiel wird überprüft, ob die von der ExcelFile-Variable angegebene Excel-Arbeitsmappendatei vorhanden ist. Daraufhin wird der boolesche Wert der ExcelFileExists-Variable auf das Ergebnis festgelegt. Sie können diesen booleschen Wert im Workflow des Pakets zur Verzweigung verwenden.

So konfigurieren Sie dieses Skripttaskbeispiel

1. Fügen Sie dem Paket eine neue Skriptaufgabe hinzu, und ändern Sie den Namen in ExcelFileExists.

2. Klicken Sie im Skripttask-Editor auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Geben Sie ExcelFileein.

     Oder

   • Klicken Sie neben dem Eigenschaftenfeld auf die Schaltfläche mit den Auslassungspunkten (...), und wählen Sie im Dialogfeld "Variablen auswählen" die ExcelFile Variable aus.

3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Geben Sie ExcelFileExistsein.

     Oder

   • Klicken Sie neben dem Eigenschaftenfeld auf die Schaltfläche mit den Auslassungspunkten (...), und wählen Sie im Dialogfeld "Variablen auswählen" die ExcelFileExists Variable aus.

4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

5. Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.IO-Namespace hinzu.

6. Fügen Sie den folgenden Code hinzu.

Codebeispiel 1

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    If File.Exists(fileToTest) Then  
      Dts.Variables("ExcelFileExists").Value = True  
    Else  
      Dts.Variables("ExcelFileExists").Value = False  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    string fileToTest;  
  
    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
    if (File.Exists(fileToTest))  
    {  
      Dts.Variables["ExcelFileExists"].Value = true;  
    }  
    else  
    {  
      Dts.Variables["ExcelFileExists"].Value = false;  
    }  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}  

Beschreibung zu Beispiel 2: Überprüfen, ob eine Excel-Tabelle vorhanden ist

In diesem Beispiel wird überprüft, ob das in der ExcelTable-Variable angegebene Excel-Arbeitsblatt bzw. der benannte Bereich in der Excel-Arbeitsmappendatei vorhanden ist, die in der ExcelFile-Variable angegeben wurde. Daraufhin wird der boolesche Wert der ExcelTableExists-Variable auf das Ergebnis festgelegt. Sie können diesen booleschen Wert im Workflow des Pakets zur Verzweigung verwenden.

So konfigurieren Sie dieses Skripttaskbeispiel

1. Fügen Sie dem Paket eine neue Skriptaufgabe hinzu, und ändern Sie den Namen in ExcelTableExists.

2. Klicken Sie im Skripttask-Editor auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Eingeben ExcelTable und ExcelFile durch Kommas getrennt.

     Oder

   • Klicken Sie neben dem Eigenschaftenfeld auf die Schaltfläche mit den Auslassungspunkten (...), und wählen Sie im Dialogfeld "Variablen auswählen" die ExcelTable Variablen aus ExcelFile .

3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Geben Sie ExcelTableExistsein.

     Oder

   • Klicken Sie neben dem Eigenschaftenfeld auf die Schaltfläche mit den Auslassungspunkten (...), und wählen Sie im Dialogfeld "Variablen auswählen" die ExcelTableExists Variable aus.

4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

5. Fügen Sie der Assembly System.Xml im Skriptprojekt einen Verweis hinzu.

6. Fügen Sie am Anfang der Skriptdatei Imports-Anweisungen für den System.IO-Namespace und den System.Data.OleDb-Namespace hinzu.

7. Fügen Sie den folgenden Code hinzu.

Codebeispiel 2

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
    Dim tableToTest As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim excelTables As DataTable  
    Dim excelTable As DataRow  
    Dim currentTable As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    tableToTest = Dts.Variables("ExcelTable").Value.ToString  
  
    Dts.Variables("ExcelTableExists").Value = False  
    If File.Exists(fileToTest) Then  
      connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 8.0"  
      excelConnection = New OleDbConnection(connectionString)  
      excelConnection.Open()  
      excelTables = excelConnection.GetSchema("Tables")  
      For Each excelTable In excelTables.Rows  
        currentTable = excelTable.Item("TABLE_NAME").ToString  
        If currentTable = tableToTest Then  
          Dts.Variables("ExcelTableExists").Value = True  
        End If  
      Next  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
    public void Main()  
        {  
            string fileToTest;  
            string tableToTest;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable excelTables;  
            string currentTable;  
  
            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();  
  
            Dts.Variables["ExcelTableExists"].Value = false;  
            if (File.Exists(fileToTest))  
            {  
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";  
                excelConnection = new OleDbConnection(connectionString);  
                excelConnection.Open();  
                excelTables = excelConnection.GetSchema("Tables");  
                foreach (DataRow excelTable in excelTables.Rows)  
                {  
                    currentTable = excelTable["TABLE_NAME"].ToString();  
                    if (currentTable == tableToTest)  
                    {  
                        Dts.Variables["ExcelTableExists"].Value = true;  
                    }  
                }  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
  
        }  
}  

Beschreibung zu Beispiel 3: Abrufen einer Liste der Excel-Dateien in einem Ordner

In diesem Beispiel wird ein Array mit der Liste der Excel-Dateien aus dem Ordner gefüllt, der im Wert der ExcelFolder-Variable angegeben wurde. Das Array wird daraufhin in die ExcelFiles-Variable kopiert. Mithilfe des Foreach-Enumerators für Daten aus Variablen können die Dateien in dem Array durchlaufen werden.

So konfigurieren Sie dieses Skripttaskbeispiel

1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in GetExcelFiles.

2. Öffnen Sie den Skripttask-Editor, klicken Sie auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Geben Sie ExcelFolder ein.

     Oder

   • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten ( … ) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable „ExcelFolder“ aus.

3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Geben Sie ExcelFilesein.

     Oder

   • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten ( … ) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable „ExcelFiles“ aus.

4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

5. Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.IO-Namespace hinzu.

6. Fügen Sie den folgenden Code hinzu.

Codebeispiel 3

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xls"  
  
    Dim excelFolder As String  
    Dim excelFiles As String()  
  
    excelFolder = Dts.Variables("ExcelFolder").Value.ToString  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)  
  
    Dts.Variables("ExcelFiles").Value = excelFiles  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    const string FILE_PATTERN = "*.xls";  
  
    string excelFolder;  
    string[] excelFiles;  
  
    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);  
  
    Dts.Variables["ExcelFiles"].Value = excelFiles;  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}  

Alternative Lösung

Anstelle eines Skripttasks können Sie zum Sammeln einer Liste von Excel-Arbeitsmappen in einem Array auch den Foreach-Dateienumerator verwenden, um alle Excel-Dateien in einem Ordner zu durchlaufen. Weitere Informationen finden Sie unter Loop through Excel Files and Tables by Using a Foreach Loop Container (Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer).

Beschreibung zu Beispiel 4: Abrufen einer Liste der Tabellen in einer Excel-Datei

In diesem Beispiel wird ein Array mit der Liste der Arbeitsmappen und benannten Bereiche in der Excel-Arbeitsmappendatei gefüllt, der im Wert der ExcelFile-Variable angegeben wurde. Das Array wird daraufhin in die ExcelTables-Variable kopiert. Mithilfe des Foreach-Enumerators für Daten aus Variablen können die Tabellen in dem Array durchlaufen werden.

So konfigurieren Sie dieses Skripttaskbeispiel

1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in GetExcelTables.

2. Öffnen Sie den Skripttask-Editor, klicken Sie auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Geben Sie ExcelFileein.

     Oder

   • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten ( … ) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable „ExcelFiles“ aus.

3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

   • Geben Sie ExcelTablesein.

     Oder

   • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten ( … ) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable „ExcelTables“ aus.

4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

5. Fügen Sie dem System.Xml-Namespace einen Verweis im Skriptprojekt hinzu.

6. Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.Data.OleDb-Namespace hinzu.

7. Fügen Sie den folgenden Code hinzu.

Code zu Beispiel 4

Public Class ScriptMain  
  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("ExcelFile").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  
      currentTable = tableInFile.Item("TABLE_NAME").ToString  
      excelTables(tableIndex) = currentTable  
      tableIndex += 1  
    Next  
  
    Dts.Variables("ExcelTables").Value = excelTables  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            string excelFile;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable tablesInFile;  
            int tableCount = 0;  
            string currentTable;  
            int tableIndex = 0;  
  
            string[] excelTables = new string[5];  
  
            excelFile = Dts.Variables["ExcelFile"].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;  
  
            foreach (DataRow tableInFile in tablesInFile.Rows)  
            {  
                currentTable = tableInFile["TABLE_NAME"].ToString();  
                excelTables[tableIndex] = currentTable;  
                tableIndex += 1;  
            }  
  
            Dts.Variables["ExcelTables"].Value = excelTables;  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}  

Alternative Lösung

Anstelle eines Skripttasks können Sie zum Sammeln einer Liste von Excel-Arbeitsmappen in einem Array auch den Enumerator für das Foreach-ADO.NET-Schemarowset verwenden, um alle Tabellen (d. h. Arbeitsmappen und benannte Bereiche) in einer Excel-Arbeitsmappendatei zu durchlaufen. Weitere Informationen finden Sie unter Loop through Excel Files and Tables by Using a Foreach Loop Container (Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer).

Anzeigen der Ergebnisse dieser Beispiele

Wenn Sie alle Beispiele dieses Themas im selben Paket konfiguriert haben, können Sie alle Skripttasks mit einem zusätzlichen Skripttask verbinden, der die Ausgaben aller Beispiele anzeigt.

So konfigurieren Sie einen Skripttask zum Anzeigen der Ausgabe der in diesem Thema behandelten Beispiele

1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in DisplayResults.

2. Verbinden Sie alle vier Beispielskripttasks miteinander, sodass nach dem erfolgreichen Abschluss des vorhergehenden Tasks der jeweils nächste Task ausgeführt wird, und verbinden Sie den vierten Beispieltask mit dem DisplayResults-Task.

3. Öffnen Sie den Task DisplayResults im Skripttask-Editor.

4. Klicken Sie auf der Registerkarte Skript auf ReadOnlyVariables, und fügen Sie mithilfe einer der folgenden Methoden alle sieben unter Konfigurieren eines Pakets zum Testen der Beispiele aufgeführten Variablen hinzu:

   • Geben Sie den Namen jeder Variable durch Trennzeichen getrennt ein.

     - oder -

   • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten ( … ) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variablen aus.

5. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

6. Fügen Sie am Anfang der Skriptdatei Imports-Anweisungen für den Microsoft.VisualBasic-Namespace und den System.Windows.Forms-Namespace hinzu.

7. Fügen Sie den folgenden Code hinzu.

8. Führen Sie das Paket aus, und überprüfen Sie die in dem Meldungsfeld angezeigten Ergebnisse.

Code zum Anzeigen der Ergebnisse

Public Class ScriptMain  
  Public Sub Main()  
    Const EOL As String = ControlChars.CrLf  
  
    Dim results As String  
    Dim filesInFolder As String()  
    Dim fileInFolder As String  
    Dim tablesInFile As String()  
    Dim tableInFile As String  
  
    results = _  
      "Final values of variables:" & EOL & _  
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _  
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _  
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _  
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _  
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _  
      EOL  
  
    results &= "Excel files in folder: " & EOL  
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())  
    For Each fileInFolder In filesInFolder  
      results &= " " & fileInFolder & EOL  
    Next  
    results &= EOL  
  
    results &= "Excel tables in file: " & EOL  
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())  
    For Each tableInFile In tablesInFile  
      results &= " " & tableInFile & EOL  
    Next  
  
    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            const string EOL = "\r";  
  
            string results;  
            string[] filesInFolder;  
            //string fileInFolder;  
            string[] tablesInFile;  
            //string tableInFile;  
  
            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;  
  
            results += "Excel files in folder: " + EOL;  
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);  
            foreach (string fileInFolder in filesInFolder)  
            {  
                results += " " + fileInFolder + EOL;  
            }  
            results += EOL;  
  
            results += "Excel tables in file: " + EOL;  
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);  
            foreach (string tableInFile in tablesInFile)  
            {  
                results += " " + tableInFile + EOL;  
            }  
  
            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}  

Mit Integrationsdiensten auf dem neuesten Stand bleiben
Die neuesten Downloads, Artikel, Beispiele und Videos von Microsoft sowie ausgewählte Lösungen aus der Community finden Sie auf der Seite Integration Services auf MSDN:

Besuchen Sie die Integration Services-Seite auf MSDN

Abonnieren Sie die auf der Seite verfügbaren RSS-Feeds, um automatische Benachrichtigungen zu diesen Updates zu erhalten.

Weitere Informationen

Excel-Verbindungs-Manager
Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer