SSIS作业因带密码Excel源文件失败，如何配置密码验证？

这个问题我碰到过好多次了，带密码的Excel确实是SSIS里的一个小坑，下面给你几个靠谱的解决方案，按推荐优先级排序：

1. 利用Excel连接管理器的原生密码属性（仅限特定驱动） #

如果你的SSIS包用的是Microsoft Jet OLEDB 4.0驱动（针对旧版.xls文件），这是最简单的方法：

• 右键点击Excel连接管理器，选择「属性」；
• 在属性窗口里找到Password字段，输入Excel文件的密码即可。

注意：这个方法对**ACE驱动（Microsoft.ACE.OLEDB.12.0/16.0）**无效，因为ACE驱动设计上不支持通过连接字符串传递密码，强行设置也不会生效。

2. 用脚本任务生成无密码临时文件（ACE驱动首选） #

针对ACE驱动（处理.xlsx/.xlsm等新版Excel），最通用的方案是在数据流任务前加一个脚本任务，先解锁Excel并导出无密码副本，再用副本作为数据源：

• 步骤：
  1. 在SSIS包中创建两个字符串变量：User::SourceExcelPath（存储原带密码文件路径）、User::TempExcelPath（存储临时无密码文件路径）；
  2. 添加脚本任务，将这两个变量设为可读/可写；
  3. 在脚本编辑器中编写C#/VB代码，用Office Interop解锁并保存无密码副本。

示例C#脚本片段：

using Microsoft.Office.Interop.Excel;
using System.IO;
using System;

public void Main()
{
    try
    {
        string sourceFile = Dts.Variables["User::SourceExcelPath"].Value.ToString();
        string tempFile = Path.Combine(Path.GetTempPath(), $"Temp_{Guid.NewGuid()}.xlsx");

        Application excelApp = new Application { Visible = false };
        Workbook workbook = excelApp.Workbooks.Open(sourceFile, Password: "你的Excel密码");
        
        // 保存为无密码的OpenXML格式文件
        workbook.SaveAs(tempFile, XlFileFormat.xlOpenXMLWorkbook, Password: "");
        
        workbook.Close(false);
        excelApp.Quit();
        
        // 释放COM对象，避免内存泄漏
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

        Dts.Variables["User::TempExcelPath"].Value = tempFile;
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(0, "解锁Excel失败", ex.Message, string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

• 注意事项：部署SSIS包的服务器必须安装对应版本的Office组件，并且要确保脚本任务的运行权限足够访问Office组件。另外，处理完后可以再加一个脚本任务删除临时文件。

3. 使用第三方SSIS组件（省心但付费） #

如果不想写代码，有些商业SSIS组件（比如CozyRoc Excel Source、Devart Excel Source）原生支持读取带密码的Excel文件，只需要在连接管理器里配置密码即可。

• 优点：集成度高，无需额外开发，兼容性好；
• 缺点：需要付费购买，增加项目成本。

4. 提前预处理Excel文件（业务流程优化） #

如果业务场景允许，最好的方式是从源头解决：

• 要求提供Excel文件的业务方去掉密码；
• 或者用PowerShell脚本提前批量解锁文件，再交给SSIS处理。

示例PowerShell脚本片段：

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open("C:\Data\Protected.xlsx", $null, $null, $null, "你的密码")
$workbook.SaveAs("C:\Data\Unprotected.xlsx", [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
$workbook.Close()
$excel.Quit()
# 释放COM对象
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null

关键安全提示 #

无论用哪种方法，绝对不要硬编码密码！把密码存储在SSIS配置文件、SQL Server SSISDB的环境变量、或者Windows凭据管理器中，通过变量引用，避免密码泄露。

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