SSIS动态加载不同结构Excel文件报错求助（SQL Server 2014/VS2013）

首先，你遇到的“Exception has been thrown by the target of an invocation”是个典型的包装型异常，底层肯定藏着具体的错误原因，咱们一步步拆解解决：

1. 优先排查驱动兼容性问题 #

你当前用的Microsoft.Jet.OLEDB.4.0是32位专属驱动，仅支持Excel 97-2003格式（.xls），但你的文件是.xlsx，再加上SQL Server 2014默认是64位环境，这大概率是核心问题之一：

• 替换驱动为Microsoft.ACE.OLEDB.12.0，它支持32/64位，兼容Excel 2007+所有格式（.xlsx/.xlsb等）
• 修改后的OpenRowset语句：

  INSERT INTO <rawdatatable> 
  SELECT * 
  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                  'Excel 12.0 Xml; Database=D:\SSIS\FileToLoad.xlsx; HDR=YES', -- HDR=YES表示第一行是列名
                  'SELECT * FROM [Sheet1$]')
  

• 同时在SSIS项目设置里，把Run64BitRuntime改为False（ACE驱动的64位版本需要单独安装，开发阶段用32位更稳妥）：右键项目 → 属性 → 配置属性 → 调试 → Run64BitRuntime → 设为False

2. 解决权限与驱动安装问题 #

• 开发环境：确保你的Windows账户对D:\SSIS文件夹有读写权限，并且已经安装了Microsoft Access Database Engine 2010 Redistributable（对应ACE 12.0，适配SQL Server 2014），注意如果已经装了32位Office，要装32位的ACE驱动，反之装64位（不能混装）
• 生产环境：如果用SQL Server Agent执行包，Agent服务账户需要有Excel文件路径的读写权限，同时服务器上也要安装对应位数的ACE驱动

3. 处理多结构Excel的列匹配问题 #

你有三种不同列数的Excel（50/55/60列），直接用SELECT *必然会导致和原始数据表的列数不匹配，这也是报错的潜在原因：

• 方案一：为每种结构的Excel创建对应的原始数据表（比如RawData_50Cols、RawData_55Cols、RawData_60Cols），在循环时先判断Excel的列数，再选择对应的目标表执行Insert
• 方案二：动态生成Insert语句，先通过ACE驱动读取Excel的列信息，再构建匹配原始表列的Insert语句（适合原始表能兼容所有列的情况，比如原始表有60列，前50/55列对应Excel的列，剩下的设为NULL）

4. 捕获具体错误信息（关键！） #

当前的错误日志太笼统，在Script Task里添加try-catch块来捕获底层异常：
比如在C#的Script Task中：

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.SqlClient;

public void Main()
{
    string connStr = "你的SQL Server连接字符串";
    string excelPath = Dts.Variables["User::FileToLoad"].Value.ToString();
    string sql = $"INSERT INTO <rawdatatable> SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml; Database={excelPath}; HDR=YES','SELECT * FROM [Sheet1$]')";

    try
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        // 将具体错误写入SSIS日志
        Dts.Events.FireError(0, "Script Task Error", ex.ToString(), string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

这样你就能在SSIS的执行日志里看到具体错误（比如驱动未找到、列数不匹配、文件被锁定等）

额外注意事项 #

• 确认Excel文件没有被其他程序锁定（比如打开着Excel文件）
• 如果Excel的Sheet名不是固定的Sheet1，需要动态获取Sheet名（可以通过ACE驱动读取Excel的架构信息来实现）

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