如何在SSIS中获取作为源助手的Excel文件的列名？

Hey there! Let's work through your SSIS scenario where you're migrating data from an Excel file with fixed column counts but changing column names to SQL Server. The key here is first grabbing those dynamic column names, then building a flexible data flow to map them correctly. Here are two reliable approaches to get you started:

Approach 1: Use a Script Task to Extract Column Names #

This is the most flexible method, especially if you need to reuse the column names elsewhere in your package.

Step-by-Step Setup: #

1. Create Variables: Add two package-level variables:

   • User::ExcelConnectionString (String): Store your Excel connection string (e.g., Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourFile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;" – make sure HDR=YES is set to read the first row as headers).
   • User::ExcelColumnNames (String): To store the comma-separated list of column names.

2. Add a Script Task: Drag a Script Task onto your Control Flow, connect it to your data flow task, and configure it to read User::ExcelConnectionString as a read-only variable, and User::ExcelColumnNames as a read-write variable.

3. Write the Script: Open the script editor (example uses C#) and paste this code to extract column names:

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Runtime;

public void Main()
{
    string connString = Dts.Variables["User::ExcelConnectionString"].Value.ToString();
    List<string> colNames = new List<string>();

    try
    {
        using (OleDbConnection excelConn = new OleDbConnection(connString))
        {
            excelConn.Open();
            // Get schema for the target sheet (replace "Sheet1" with your sheet name, or make it a variable)
            DataTable schemaTable = excelConn.GetOleDbSchemaTable(
                OleDbSchemaGuid.Columns,
                new object[] { null, null, "Sheet1", null }
            );

            foreach (DataRow row in schemaTable.Rows)
            {
                colNames.Add(row["COLUMN_NAME"].ToString());
            }
        }

        // Store column names as a comma-separated string
        Dts.Variables["User::ExcelColumnNames"].Value = string.Join(",", colNames);
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(0, "Script Task Error", ex.Message, string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

4. Verify: Run the package and check that User::ExcelColumnNames is populated with your dynamic headers.

Approach 2: Extract Columns via Execute SQL Task #

If you prefer working with SQL over scripts, you can query the Excel schema directly:

1. Configure an Excel Connection Manager: Point it to your target Excel file.
2. Add an Execute SQL Task: Set its connection to your Excel connection manager, and use this query:

SELECT COLUMN_NAME 
FROM [Sheet1$] 
WHERE 1=0

(Note: The WHERE 1=0 ensures you only get the column structure, no data)
3. Map Results: Set the result set to "Full result set" and map it to an object variable. You can then loop through this object variable to extract individual column names (use a Foreach Loop Container with an ADO enumerator).

Next Steps: Dynamic Data Flow Mapping #

Once you have the column names, you need to dynamically map them to your SQL Server table. Here's how:

• Dynamic SQL Insert: Use an Execute SQL Task to build an INSERT statement using the User::ExcelColumnNames variable. For example:

  INSERT INTO YourSQLTable ([{ExcelColumnNames}])
  SELECT [{ExcelColumnNames}] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=C:\YourFile.xlsx', 'SELECT * FROM [Sheet1$]')
  

  (Replace {ExcelColumnNames} with your variable value, make sure to handle square brackets for column names with spaces)
• Script Component as Source: Use a Script Component in your Data Flow to dynamically create output columns based on the stored column names, then read and pass through the Excel data.

Key Notes: #

• Always ensure HDR=YES in your Excel connection string – this tells SSIS the first row is a header.
• For .xls files, use Microsoft.Jet.OLEDB.4.0 instead of Microsoft.ACE.OLEDB.12.0.
• If your Excel sheet name has spaces, wrap it in square brackets (e.g., [Sales Data$]).

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