SSIS导入含未知数量工作表的大型Excel文件的异常处理实现方法

你设想的方案完全可行，无需提前调用接口枚举所有工作表名，仅靠循环+异常捕获逻辑即可实现，性能优于提前提取工作表名的方案，也满足全流程在SSIS包内完成的要求。

1. 前期变量准备 #

先在SSIS包中创建以下变量：

• 字符串类型ExcelFilePath：存储外层循环遍历到的当前Excel文件全路径
• 字符串类型CurrentSheetName：存储当前要读取的工作表名，默认初始值设为Sheet1$（Excel工作表名在SSIS中调用需带$后缀）
• 32位整数类型SheetIndex：存储当前循环的工作表序号，默认初始值设为1
• 布尔类型SheetExists：标记当前工作表是否存在，默认初始值设为True
• 字符串类型ExcelConnectionString：动态构造的Excel连接字符串，表达式绑定为：
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::ExcelFilePath] + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"
  如果是旧版.xls格式，修改Extended Properties中的版本参数即可。

2. 外层Foreach循环配置 #

使用Foreach文件枚举器，指定Excel文件存放目录，筛选对应后缀（.xlsx/.xls），将遍历到的文件全路径赋值给@[User::ExcelFilePath]变量。

3. 内层For循环配置 #

循环条件设置为@[User::SheetExists] == True && @[User::SheetIndex] <=20，由于已知工作表最多不超过20个，加上上限避免极端情况死循环。
循环内部首先放置一个脚本任务，用于根据序号生成当前工作表名，C#代码参考：

public void Main()
{
    Dts.Variables["User::CurrentSheetName"].Value = $"Sheet{Dts.Variables["User::SheetIndex"].Value}$";
    Dts.TaskResult = (int)ScriptResults.Success;
}

需在脚本任务的只读变量中添加SheetIndex，读写变量中添加CurrentSheetName。

4. 数据流任务配置 #

脚本任务后放置数据流任务，数据源选择Excel源，连接管理器使用动态绑定了ExcelFilePath的Excel连接管理器，数据访问模式选择「表名或视图名变量」，变量选择@[User::CurrentSheetName]，目标选择对应SQL Server表，完成列映射即可。

5. 异常捕获与循环终止配置 #

右键点击数据流任务，选择「事件处理程序」，添加OnError事件处理逻辑：

• 放置一个脚本任务，将SheetExists变量设为False，同时禁用错误冒泡，避免当前文件的错误影响外层循环处理其他文件，C#代码参考：

public void Main()
{
    Dts.Variables["User::SheetExists"].Value = false;
    Dts.Variables["System::Propagate"].Value = false;
    Dts.TaskResult = (int)ScriptResults.Success;
}

需在脚本任务的读写变量中添加SheetExists和系统变量Propagate。

• 在内层For循环的末尾添加表达式任务，执行@[User::SheetIndex] = @[User::SheetIndex] + 1，序号自增用于下一次循环。

6. 变量重置配置 #

在内层For循环结束后、外层Foreach循环的内部，添加两个表达式任务，重置参数用于处理下一个文件：

• @[User::SheetIndex] = 1
• @[User::SheetExists] = True

• 若使用64位SQL Server，可在项目属性的「调试」选项下将Run64BitRuntime设为True，同时安装64位ACE OLEDB驱动，性能比32位运行模式高30%左右。
• 数据流任务中可将「默认缓冲区大小」调大到10485760（10MB），「默认缓冲区行数」调为10000，导入速度会明显提升。
• 导入前可临时禁用目标表的非聚集索引、触发器，导入完成后再重建，性能提升更明显。

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