SSIS使用表达式配置Excel连接管理器报错：无法更新，数据库或对象只读

问题背景 #

部署在SERV_SSIS_CATALOG的SSIS包，需通过项目参数动态指定SERV_FILE服务器上的Excel文件路径，实现SQL数据写入Excel。静态路径时包运行正常，但配置ExcelFilePath/ConnectionString表达式后：

• 初始无法预览Excel源数据，添加IMEX=1后可预览，但写入时报错：

Load Excel Base File:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80040E09 Description: "Cannot update. Database or object is read-only.".

• 已确认SERV_FILE上Excel文件及共享文件夹权限为完全控制，但执行仍失败；且动态表达式下，SSIS Catalog中无法看到ConnectionString值。

排查与修复步骤 #

1. 修正ConnectionString表达式的格式 #

OLEDB连接字符串的Extended Properties部分需正确转义引号，且确保ReadOnly=0和IMEX=0（写入模式）被正确嵌入：

• 正确的表达式示例（SSIS表达式语法，双引号用两个转义）：

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[Project::ExcelFilePath] + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=0;ReadOnly=0;"""

• 注意：IMEX=1仅适用于读取混合数据类型的场景，写入时必须设为IMEX=0，否则驱动会强制只读模式；HDR=YES表示第一行是表头，根据实际需求调整。

2. 确认执行账户的实际权限 #

• 若通过SQL Server Agent执行包，需检查作业步骤的运行账户（如代理账户）对SERV_FILE的共享文件夹及Excel文件拥有完全控制权限（包括创建、修改、删除临时文件的权限）。
• 若手动执行包，需确认当前登录用户的权限覆盖SERV_FILE的文件系统权限（包括NTFS权限和共享权限）。

3. 检查Excel文件是否被锁定 #

• 确认SERV_FILE上的Excel文件未被其他程序打开，或之前执行失败导致文件句柄残留。可通过Windows资源管理器的「打开文件」功能或Process Explorer工具排查锁定情况。

4. 关于SSIS Catalog中ConnectionString不可见的说明 #

动态表达式生成的ConnectionString是在包执行时计算的，SSIS Catalog仅存储表达式模板而非最终值，此为正常现象，无需额外处理。

替代方案（绕过SSIS Excel连接管理器限制） #

若上述步骤仍无法解决问题，可尝试以下替代方法：

方法1：PowerShell脚本导出 #

使用PowerShell的ImportExcel模块（需提前安装）直接从SQL查询数据并写入Excel，示例脚本：

# 安装模块（首次运行）
# Install-Module -Name ImportExcel -Scope CurrentUser

# 连接SQL Server查询数据
$sqlQuery = "SELECT * FROM YourTable"
$serverInstance = "YourSQLServer"
$database = "YourDB"
$data = Invoke-SqlCmd -ServerInstance $serverInstance -Database $database -Query $sqlQuery

# 写入Excel文件（共享路径）
$excelPath = "\\SERV_FILE\SharedFolder\Output.xlsx"
$data | Export-Excel -Path $excelPath -AutoSize -TableName "OutputData" -Force

将脚本部署为SQL Server Agent的PowerShell作业步骤，使用有相应权限的账户运行。

方法2：SSIS Script Task自定义写入 #

在SSIS中添加Script Task，使用EPPlus或NPOI等第三方库直接操作Excel文件：

• 引用EPPlus库（需将DLL放入SSIS项目的引用，或部署到服务器的GAC）
• C#代码示例：

using OfficeOpenXml;
using System.Data.SqlClient;
using System.Data;
using System.IO;

public void Main()
{
    string excelPath = Dts.Variables["User::ExcelFilePath"].Value.ToString();
    string connString = "Data Source=YourSQLServer;Initial Catalog=YourDB;Integrated Security=True";
    string sqlQuery = "SELECT * FROM YourTable";

    // 读取SQL数据
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);
        da.Fill(dt);
    }

    // 写入Excel
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 根据许可调整
    using (ExcelPackage pkg = new ExcelPackage(new FileInfo(excelPath)))
    {
        ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Output");
        ws.Cells["A1"].LoadFromDataTable(dt, true);
        pkg.Save();
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

方法3：SQL Server Ad Hoc分布式查询 #

启用Ad Hoc Distributed Queries后，直接通过T-SQL写入Excel（需注意驱动版本和权限）：

-- 启用Ad Hoc Distributed Queries
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

-- 写入Excel
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                       'Excel 12.0 Xml;HDR=YES;Database=\\SERV_FILE\SharedFolder\Output.xlsx', 
                       'SELECT * FROM [Sheet1$]')
SELECT Column1, Column2 FROM YourSQLTable;

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