查询值和目标字段的数量不同- C#脚本任务SSIS -使用动态列将SQL的结果导出到Excel

using System;
using System.Data;
using System.Data.OleDb;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

public class ScriptMain
{
    public void Main()
    {
        try
        {
            // 设置连接字符串
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\path\\to\\excel_file.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;'";
            
            // 创建Excel连接
            OleDbConnection connection = new OleDbConnection(connectionString);
            
            // 打开连接
            connection.Open();
            
            // 创建查询语句
            string query = "SELECT column1, column2, column3 FROM table";
            
            // 创建命令对象
            OleDbCommand command = new OleDbCommand(query, connection);
            
            // 执行查询
            OleDbDataReader reader = command.ExecuteReader();
            
            // 创建Excel文件
            string excelFilePath = "C:\\path\\to\\output_file.xlsx";
            OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'");
            excelConnection.Open();
            
            // 创建Excel表格
            OleDbCommand createTableCommand = new OleDbCommand("CREATE TABLE [Sheet1] (column1 VARCHAR, column2 VARCHAR, column3 VARCHAR)", excelConnection);
            createTableCommand.ExecuteNonQuery();
            
            // 写入查询结果到Excel文件
            while (reader.Read())
            {
                // 动态添加列
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    string columnName = reader.GetName(i);
                    if (!ColumnExists(columnName, excelConnection))
                    {
                        OleDbCommand addColumnCommand = new OleDbCommand("ALTER TABLE [Sheet1] ADD COLUMN [" + columnName + "] VARCHAR", excelConnection);
                        addColumnCommand.ExecuteNonQuery();
                    }
                }
                
                // 写入数据
                OleDbCommand insertCommand = new OleDbCommand("INSERT INTO [Sheet1] VALUES (?, ?, ?)", excelConnection);
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    insertCommand.Parameters.AddWithValue("?", reader[i]);
                }
                insertCommand.ExecuteNonQuery();
            }
            
            // 关闭连接
            reader.Close();
            connection.Close();
            excelConnection.Close();
            
            // 完成任务
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)
        {
            // 处理异常
            MessageBox.Show(ex.Message);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }
    
    private bool ColumnExists(string columnName, OleDbConnection connection)
    {
        DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, "Sheet1$", columnName });
        return (schemaTable.Rows.Count > 0);
    }
    
    public enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    }
}