以指令碼工作處理 Excel 檔案Working with Excel Files with the Script Task

APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

Integration ServicesIntegration Services 提供 Excel 連接管理員、Excel 來源和 Excel 目的地,以處理 MicrosoftMicrosoft Excel 檔案格式試算表中儲存的資料。provides the Excel connection manager, Excel source, and Excel destination for working with data stored in spreadsheets in the MicrosoftMicrosoft Excel file format. 本主題所述的技術會使用指令碼工作取得有關可用 Excel 資料庫 (活頁簿檔案) 與資料表 (工作表與具名範圍) 的相關資訊。The techniques described in this topic use the Script task to obtain information about available Excel databases (workbook files) and tables (worksheets and named ranges).

重要

如需連接至 Excel 檔案,以及將資料從 Excel 檔案載入或載入至 Excel 檔案的限制與已知問題的詳細資訊,請參閱使用 SQL Server Integration Services (SSIS) 將資料從 Excel 載入或載入至 ExcelFor detailed info about connecting to Excel files, and about limitations and known issues for loading data from or to Excel files, see Load data from or to Excel with SQL Server Integration Services (SSIS).

提示

如果您想要建立可在多個套件之間重複使用的工作,請考慮使用此指令碼工作範例中的程式碼作為自訂工作的起點。If you want to create a task that you can reuse across multiple packages, consider using the code in this Script task sample as the starting point for a custom task. 如需詳細資訊,請參閱 開發自訂工作For more information, see Developing a Custom Task.

設定套件以測試範例Configuring a Package to Test the Samples

您可以設定單一封裝以測試本主題中的所有範例。You can configure a single package to test all the samples in this topic. 範例使用許多相同的封裝變數與相同的 .NET Framework.NET Framework 類別。The samples use many of the same package variables and the same .NET Framework.NET Framework classes.

設定封裝與本主題中的範例搭配使用To configure a package for use with the examples in this topic

  1. Integration ServicesIntegration Services 中建立新的 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 專案,並開啟預設封裝以進行編輯。Create a new Integration ServicesIntegration Services project in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) and open the default package for editing.

  2. 變數Variables. 開啟 [變數] 視窗,並定義下列變數:Open the Variables window and define the following variables:

    • ExcelFile,類型為 StringExcelFile, of type String. 輸入現有 Excel 活頁簿的完整路徑與檔案名稱。Enter the complete path and filename to an existing Excel workbook.

    • ExcelTable,類型為 StringExcelTable, of type String. 輸入以 ExcelFile 變數值命名之活頁簿中,現有工作表或具名範圍的名稱。Enter the name of an existing worksheet or named range in the workbook named in the value of the ExcelFile variable. 此值區分大小寫。This value is case-sensitive.

    • ExcelFileExists,類型為 BooleanExcelFileExists, of type Boolean.

    • ExcelTableExists,類型為 BooleanExcelTableExists, of type Boolean.

    • ExcelFolder,類型為 StringExcelFolder, of type String. 輸入含有至少一個 Excel 活頁簿的資料夾完整路徑。Enter the complete path of a folder that contains at least one Excel workbook.

    • ExcelFiles,類型為 ObjectExcelFiles, of type Object.

    • ExcelTables,類型為 ObjectExcelTables, of type Object.

  3. Imports 陳述式Imports statements. 大部分的程式碼範例都需要您在指令碼檔案最上方匯入下列一或兩個 .NET Framework.NET Framework 命名空間:Most of the code samples require you to import one or both of the following .NET Framework.NET Framework namespaces at the top of your script file:

    • System.IO,處理檔案系統作業。System.IO, for file system operations.

    • System.Data.OleDb,開啟 Excel 檔案作為資料來源。System.Data.OleDb, to open Excel files as data sources.

  4. 參考References. 從 Excel 檔案讀取結構描述資訊的程式碼範例,在指令碼專案中需要有 System.Xml 命名空間的參考。The code samples that read schema information from Excel files require an additional reference in the script project to the System.Xml namespace.

  5. 請使用 [選項] 對話方塊中 [一般] 頁面上的 [指令碼語言] 選項,為指令碼元件設定預設的指令碼語言。Set the default scripting language for the Script component by using the Scripting language option on the General page of the Options dialog box. 如需相關資訊,請參閱 General PageFor more information, see General Page.

範例 1 描述:檢查 Excel 檔案是否存在Example 1 Description: Check Whether an Excel File Exists

此範例會判斷 ExcelFile 變數中指定的 Excel 活頁簿檔案是否存在,然後將 ExcelFileExists 變數的布林值設定為結果。This example determines whether the Excel workbook file specified in the ExcelFile variable exists, and then sets the Boolean value of the ExcelFileExists variable to the result. 您可以為封裝工作流程中的分支使用此布林值。You can use this Boolean value for branching in the workflow of the package.

設定此指令碼工作範例To configure this Script Task example

  1. 將新指令碼工作新增至套件,並將其名稱變更為 ExcelFileExistsAdd a new Script task to the package and change its name to ExcelFileExists.

  2. 在 [指令碼工作編輯器] 的 [指令碼] 索引標籤上,按一下 [ReadOnlyVariables] ,並使用下列其中一項方法輸入屬性值:In the Script Task Editor, on the Script tab, click ReadOnlyVariables and enter the property value using one of the following methods:

    • 鍵入 ExcelFileType ExcelFile.

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelFile] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelFile variable.

  3. 按一下 ReadWriteVariables,並使用下列其中一項方法輸入屬性值:Click ReadWriteVariables and enter the property value using one of the following methods:

    • 鍵入 ExcelFileExistsType ExcelFileExists.

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelFileExists] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelFileExists variable.

  4. 按一下 [編輯指令碼] ,以開啟指令碼編輯器。Click Edit Script to open the script editor.

  5. 在指令檔最上方新增 System.IO 命名空間的 Imports 陳述式。Add an Imports statement for the System.IO namespace at the top of the script file.

  6. 加入下列程式碼。Add the following code.

範例 1 程式碼Example 1 Code

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;  
  }  
}  

範例 2 描述:檢查 Excel 資料表是否存在Example 2 Description: Check Whether an Excel Table Exists

此範例會判斷 ExcelTable 變數中指定的 Excel 工作表或具名範圍是否存在於 ExcelFile 變數中指定的 Excel 活頁簿檔案,然後將 ExcelTableExists 變數的布林值設定為結果。This example determines whether the Excel worksheet or named range specified in the ExcelTable variable exists in the Excel workbook file specified in the ExcelFile variable, and then sets the Boolean value of the ExcelTableExists variable to the result. 您可以為封裝工作流程中的分支使用此布林值。You can use this Boolean value for branching in the workflow of the package.

設定此指令碼工作範例To configure this Script Task example

  1. 將新指令碼工作新增至套件,並將其名稱變更為 ExcelTableExistsAdd a new Script task to the package and change its name to ExcelTableExists.

  2. 在 [指令碼工作編輯器] 的 [指令碼] 索引標籤上,按一下 ReadOnlyVariables,並使用下列其中一項方法輸入屬性值:In the Script Task Editor, on the Script tab, click ReadOnlyVariables and enter the property value using one of the following methods:

    • 鍵入以逗號分隔的 ExcelTableExcelFileType ExcelTable and ExcelFile separated by commas .

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelTable] 和 [ExcelFile] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelTable and ExcelFile variables.

  3. 按一下 ReadWriteVariables,並使用下列其中一項方法輸入屬性值:Click ReadWriteVariables and enter the property value using one of the following methods:

    • 鍵入 ExcelTableExistsType ExcelTableExists.

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelTableExists] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelTableExists variable.

  4. 按一下 [編輯指令碼] ,以開啟指令碼編輯器。Click Edit Script to open the script editor.

  5. 在指令碼專案中新增 System.Xml 組件的參考。Add a reference to the System.Xml assembly in the script project.

  6. 在指令檔頂端,針對 System.IOSystem.Data.OleDb 命名空間新增 Imports 陳述式。Add Imports statements for the System.IO and System.Data.OleDb namespaces at the top of the script file.

  7. 加入下列程式碼。Add the following code.

範例 2 程式碼Example 2 Code

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.ACE.OLEDB.12.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 12.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.ACE.OLEDB.12.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 12.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;  
  
        }  
}  

範例 3 描述:取得資料夾中的 Excel 檔案清單Example 3 Description: Get a List of Excel Files in a Folder

此範例會使用在 ExcelFolder 變數值中指定的資料夾內所找到的 Excel 檔案清單,來填滿陣列,然後將陣列複製到 ExcelFiles 變數中。This example fills an array with the list of Excel files found in the folder specified in the value of the ExcelFolder variable, and then copies the array into the ExcelFiles variable. 您可以使用 Foreach From Variable 列舉值來反覆運算陣列中的檔案。You can use the Foreach from Variable enumerator to iterate over the files in the array.

設定此指令碼工作範例To configure this Script Task example

  1. 將新指令碼工作新增至套件,並將其名稱變更為 GetExcelFilesAdd a new Script task to the package and change its name to GetExcelFiles.

  2. 開啟 [指令碼工作編輯器] 的 [指令碼] 索引標籤,並按一下 [ReadOnlyVariables] ,然後使用下列其中一項方法輸入屬性值:Open the Script Task Editor, on the Script tab, click ReadOnlyVariables and enter the property value using one of the following methods:

    • 鍵入 ExcelFolderType ExcelFolder

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelFolder] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelFolder variable.

  3. 按一下 ReadWriteVariables,並使用下列其中一項方法輸入屬性值:Click ReadWriteVariables and enter the property value using one of the following methods:

    • 鍵入 ExcelFilesType ExcelFiles.

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelFiles] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelFiles variable.

  4. 按一下 [編輯指令碼] ,以開啟指令碼編輯器。Click Edit Script to open the script editor.

  5. 在指令檔最上方新增 System.IO 命名空間的 Imports 陳述式。Add an Imports statement for the System.IO namespace at the top of the script file.

  6. 加入下列程式碼。Add the following code.

範例 3 程式碼Example 3 Code

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xlsx"  
  
    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 = "*.xlsx";  
  
    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;  
  }  
}  

替代方案Alternate Solution

您也可以使用 ForEach 檔案列舉值反覆運算資料夾中的所有 Excel 檔案,以代替使用指令碼工作將 Excel 檔案清單蒐集到陣列中的方式。Instead of using a Script task to gather a list of Excel files into an array, you can also use the ForEach File enumerator to iterate over all the Excel files in a folder. 如需詳細資訊,請參閱使用 Foreach 迴圈容器來執行 Excel 檔案和資料表迴圈For more information, see Loop through Excel Files and Tables by Using a Foreach Loop Container.

範例 4 描述:取得 Excel 檔案中的資料表清單Example 4 Description: Get a List of Tables in an Excel File

此範例會使用在 ExcelFile 變數值中指定的 Excel 活頁簿檔案內找到的工作表清單和具名範圍,來填滿陣列,然後將陣列複製到 ExcelTables 變數中。This example fills an array with the list of worksheets and named ranges found in the Excel workbook file specified by the value of the ExcelFile variable, and then copies the array into the ExcelTables variable. 您可以使用 Foreach From Variable 列舉值來反覆運算陣列中的資料表。You can use the Foreach from Variable Enumerator to iterate over the tables in the array.

注意

Excel 活頁簿中資料表清單包含活頁簿 (具有 $ 後置詞) 及具名範圍。The list of tables in an Excel workbook includes both worksheets (which have the $ suffix) and named ranges. 如果您必須只篩選清單中的工作表或具名範圍,必須加入其他程式碼以達成此目的。If you have to filter the list for only worksheets or only named ranges, you may have to add additional code for this purpose.

設定此指令碼工作範例To configure this Script Task example

  1. 將新指令碼工作新增至套件,並將其名稱變更為 GetExcelTablesAdd a new Script task to the package and change its name to GetExcelTables.

  2. 開啟 [指令碼工作編輯器] 的 [指令碼] 索引標籤,並按一下 ReadOnlyVariables,然後使用下列其中一項方法輸入屬性值:Open the Script Task Editor, on the Script tab, click ReadOnlyVariables and enter the property value using one of the following methods:

    • 鍵入 ExcelFileType ExcelFile.

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelFile] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelFile variable.

  3. 按一下 ReadWriteVariables,並使用下列其中一項方法輸入屬性值:Click ReadWriteVariables and enter the property value using one of the following methods:

    • 鍵入 ExcelTablesType ExcelTables.

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取 [ExcelTables] 變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, select the ExcelTablesvariable.

  4. 按一下 [編輯指令碼] ,以開啟指令碼編輯器。Click Edit Script to open the script editor.

  5. 在指令碼專案中新增 System.Xml 命名空間的參考。Add a reference to the System.Xml namespace in the script project.

  6. 在指令檔最上方新增 System.Data.OleDb 命名空間的 Imports 陳述式。Add an Imports statement for the System.Data.OleDb namespace at the top of the script file.

  7. 加入下列程式碼。Add the following code.

範例 4 程式碼Example 4 Code

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.ACE.OLEDB.12.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 12.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.ACE.OLEDB.12.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 12.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;  
        }  
}  

替代方案Alternate Solution

您也可以使用 Foreach ADO.NET 結構描述資料列集列舉值,反覆運算在 Excel 活頁簿檔案中的所有資料表 (也就是,工作表與具名範圍),以代替使用指令碼工作將 Excel 資料表清單蒐集到陣列中的方式。Instead of using a Script task to gather a list of Excel tables into an array, you can also use the ForEach ADO.NET Schema Rowset Enumerator to iterate over all the tables (that is, worksheets and named ranges) in an Excel workbook file. 如需詳細資訊,請參閱使用 Foreach 迴圈容器來執行 Excel 檔案和資料表迴圈For more information, see Loop through Excel Files and Tables by Using a Foreach Loop Container.

顯示範例的結果Displaying the Results of the Samples

如果您已在相同封裝中設定此主題中的每個範例,可以將所有的指令碼工作連接至其他顯示所有範例輸出的指令碼工作。If you have configured each of the examples in this topic in the same package, you can connect all the Script tasks to an additional Script task that displays the output of all the examples.

設定指令碼工作以顯示本主題中的範例輸出To configure a Script task to display the output of the examples in this topic

  1. 將新指令碼工作新增至套件,並將其名稱變更為 DisplayResultsAdd a new Script task to the package and change its name to DisplayResults.

  2. 依序連線這四個範例指令碼工作,好讓每個工作在前一個工作順利完成之後接著執行,然後將第四個範例工作連線至 DisplayResults 工作。Connect each of the four example Script tasks to one another, so that each task runs after the preceding task completes successfully, and connect the fourth example task to the DisplayResults task.

  3. 開啟 [指令碼工作編輯器] 中的 DisplayResults 工作。Open the DisplayResults task in the Script Task Editor.

  4. 在 [指令碼] 索引標籤上,按一下 ReadOnlyVariables 並使用下列其中一個方法,新增設定套件以測試範例中的所有七個變數:On the Script tab, click ReadOnlyVariables and use one of the following methods to add all seven variables listed in Configuring a Package to Test the Samples:

    • 輸入每個變數名稱,並以逗號分隔。Type the name of each variable separated by commas.

      -或--or-

    • 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕,然後在 [選取變數] 對話方塊中選取變數。Click the ellipsis (...) button next to the property field, and in the Select variables dialog box, selecting the variables.

  5. 按一下 [編輯指令碼] ,以開啟指令碼編輯器。Click Edit Script to open the script editor.

  6. 在指令檔頂端,針對 Microsoft.VisualBasicSystem.Windows.Forms 命名空間新增 Imports 陳述式。Add Imports statements for the Microsoft.VisualBasic and System.Windows.Forms namespaces at the top of the script file.

  7. 加入下列程式碼。Add the following code.

  8. 執行封裝並檢查在訊息方塊中顯示的結果。Run the package and examine the results displayed in a message box.

可顯示結果的程式碼Code to Display the Results

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;  
        }  
}  

另請參閱See Also

使用 SQL Server Integration Services (SSIS) 將資料從 Excel 載入或載入至 ExcelLoad data from or to Excel with SQL Server Integration Services (SSIS)
使用 Foreach 迴圈容器來執行 Excel 檔案和資料表迴圈Loop through Excel Files and Tables by Using a Foreach Loop Container