如何通过SSIS将Excel多工作表分别转换为独立CSV文件？

Got it, let's tackle this problem. The File System Task is indeed limited when it comes to handling multiple Excel sheets—so we'll use a Foreach Loop Container combined with dynamic connection managers to iterate through each sheet and export it to a separate CSV. Here's a step-by-step breakdown:

Step 1: Create Required Variables #

First, set up these SSIS variables (head to the Variables pane, right-click to add):

• varExcelFilePath: String type, set its value to the full path of your Product.xlsx (e.g., C:\YourDataFolder\Product.xlsx)
• varRawSheetName: String type, stores the raw sheet name from Excel (includes the $ suffix like Info$)
• varCleanSheetName: String type, use this expression to strip the $:
  REPLACE(@[User::varRawSheetName], "$", "")
• varCsvFilePath: String type, expression to generate the CSV path:
  LEFT(@[User::varExcelFilePath], LEN(@[User::varExcelFilePath]) - 5) + "\\" + @[User::varCleanSheetName] + ".csv"
  (This replaces the .xlsx extension with the cleaned sheet name + .csv in the same folder as your Excel file)
• Optional: varOutputFolder: String type, if you want CSVs in a different directory, set this to your target folder, then adjust varCsvFilePath to @[User::varOutputFolder] + "\\" + @[User::varCleanSheetName] + ".csv"

Step 2: Configure Foreach Loop Container #

Drag a Foreach Loop Container onto the Control Flow tab, double-click to edit:

1. In the Enumerator tab, select Foreach ADO.NET Schema Rowset Enumerator
2. Click Connections > Select your existing Excel connection manager (if you don’t have one, create it by pointing to Product.xlsx)
3. Set Schema to Tables (this enumerates all sheets/tables in the Excel file)
4. Go to the Variable Mappings tab:
   • Select varRawSheetName from the dropdown
   • Set the Index to 0 (the first column in the Tables schema is TABLE_NAME, which gives us the sheet name with its $ suffix)
5. Click OK to save the loop settings

Step 3: Dynamically Update the Excel Connection Manager #

We need the Excel source to point to the current sheet in each loop iteration:

1. Right-click your Excel connection manager > Properties
2. In the Expressions section, click the ellipsis (...)
3. Select TableName from the property dropdown, then set its expression to @[User::varRawSheetName]
4. Optional: If your Excel file path might change dynamically, set the ServerName expression to @[User::varExcelFilePath] too

Step 4: Set Up the Data Flow Task for Export #

Inside the Foreach Loop Container, drag a Data Flow Task:

1. Open the Data Flow tab
2. Drag an Excel Source onto the canvas, connect it to your dynamic Excel connection manager. It’ll automatically load the schema of the current sheet (this works best if all sheets have similar structures; if not, skip to the script task alternative below)
3. Drag a Flat File Destination onto the canvas, connect the Excel Source to it
4. Create a Flat File connection manager:
   • Choose "Delimited" as the format, pick a temporary CSV file (we’ll overwrite this dynamically)
   • Configure the delimiter, text qualifier, and other settings to match your data
5. Right-click the Flat File connection manager > Properties
6. In Expressions, set ConnectionString to @[User::varCsvFilePath]—this will point to the correct CSV file for each sheet

Step 5: Add a Safety Check (Optional) #

To avoid errors if the output folder doesn’t exist:

1. Drag a File System Task before the Foreach Loop Container
2. Set Operation to Create Directory
3. Set DestinationConnection to a folder connection pointing to your output directory (or use varOutputFolder via an expression)

Alternative: Use a Script Task for Full Control #

If your sheets have varying structures (different columns, data types), a C# Script Task is more flexible. Add varExcelFilePath and varOutputFolder as ReadOnly variables, then use this snippet:

using System.IO;
using System.Data.OleDb;

public void Main()
{
    string excelPath = Dts.Variables["User::varExcelFilePath"].Value.ToString();
    string outputFolder = Dts.Variables["User::varOutputFolder"].Value.ToString();
    
    // Create output folder if it doesn't exist
    if (!Directory.Exists(outputFolder))
        Directory.CreateDirectory(outputFolder);
    
    // Excel connection string (adjust Provider for your Excel version)
    string connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelPath};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
    
    using (OleDbConnection conn = new OleDbConnection(connString))
    {
        conn.Open();
        // Get all sheet names
        DataTable sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        
        foreach (DataRow sheet in sheets.Rows)
        {
            string sheetName = sheet["TABLE_NAME"].ToString();
            // Skip hidden sheets or system tables
            if (!sheetName.EndsWith("$")) continue;
            
            string cleanSheetName = sheetName.Replace("$", "");
            string csvPath = Path.Combine(outputFolder, $"{cleanSheetName}.csv");
            
            // Query the sheet
            string query = $"SELECT * FROM [{sheetName}]";
            using (OleDbCommand cmd = new OleDbCommand(query, conn))
            using (OleDbDataReader reader = cmd.ExecuteReader())
            using (StreamWriter writer = new StreamWriter(csvPath))
            {
                // Write header row
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    writer.Write($"\"{reader.GetName(i)}\"");
                    if (i < reader.FieldCount - 1) writer.Write(",");
                }
                writer.WriteLine();
                
                // Write data rows
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string value = reader.IsDBNull(i) ? "" : reader[i].ToString();
                        writer.Write($"\"{value.Replace("\"", "\"\"")}\"");
                        if (i < reader.FieldCount - 1) writer.Write(",");
                    }
                    writer.WriteLine();
                }
            }
        }
        conn.Close();
    }
    
    Dts.TaskResult = (int)ScriptResults.Success;
}

Note: Ensure the Microsoft Access Database Engine 2016 Redistributable is installed on your SSIS server to use the ACE provider.

内容的提问来源于stack exchange，提问作者Hadrian