在SSIS包中验证Excel文件格式的最优方案及Script task可行性咨询

可行性结论 #

Script Task/C#完全可行，且是这类自定义格式验证场景的首选方案——SSIS原生组件无法灵活满足多文件循环下的列数、数据类型、指定列非空这三类组合验证需求，而C#可以通过代码精准控制验证逻辑和性能。

兼顾简洁与性能的最优实现步骤 #

1. 预定义基准规则 #

先拿一个格式完全合规的Excel文件作为基准，提取并存储规则到SSIS变量中：

• @ExpectedColumnCount：整数类型，存储基准列数
• @ExpectedColumnTypes：字符串类型，存储列名-数据类型的字典（比如JSON序列化格式：{"ID":"Int32","Name":"String","CreateDate":"DateTime"}）
• @RequiredColumns：字符串数组类型，存储必填列名（比如["ID","Name"]）

2. 在循环中执行Script Task验证 #

在现有文件循环逻辑中插入Script Task，引用EPPlus（需将EPPlus.dll放入SSIS项目依赖或全局程序集缓存），编写以下验证逻辑：

列数一致性验证

// 获取当前Excel文件路径
string filePath = Dts.Variables["@CurrentExcelFilePath"].Value.ToString();
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
    var worksheet = package.Workbook.Worksheets.First();
    int actualColumnCount = worksheet.Dimension.End.Column;
    int expectedCount = (int)Dts.Variables["@ExpectedColumnCount"].Value;
    if (actualColumnCount != expectedCount)
    {
        Dts.Events.FireError(0, "列数验证失败", $"文件{filePath}列数为{actualColumnCount}，预期为{expectedCount}", string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
        return;
    }
}

列数据类型一致性验证

为避免全表扫描影响性能，仅读取前100行样本（可根据业务调整）验证类型：

// 反序列化基准类型字典
var expectedTypes = JsonConvert.DeserializeObject<Dictionary<string, string>>(Dts.Variables["@ExpectedColumnTypes"].Value.ToString());
var worksheet = package.Workbook.Worksheets.First();
// 获取表头列名
List<string> actualColumns = Enumerable.Range(1, worksheet.Dimension.End.Column)
                                      .Select(col => worksheet.Cells[1, col].Text)
                                      .ToList();

foreach (var colName in expectedTypes.Keys)
{
    int colIndex = actualColumns.IndexOf(colName) + 1;
    if (colIndex == 0)
    {
        Dts.Events.FireError(0, "列缺失", $"文件{filePath}缺少必填列{colName}", string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
        return;
    }
    // 验证前100行的类型
    int maxRow = Math.Min(worksheet.Dimension.End.Row, 101); // 跳过表头行
    for (int row = 2; row <= maxRow; row++)
    {
        var cell = worksheet.Cells[row, colIndex];
        string actualType = cell.Value?.GetType().Name ?? "Null";
        if (actualType != expectedTypes[colName] && !(expectedTypes[colName] == "String" && actualType == "Null"))
        {
            Dts.Events.FireError(0, "数据类型不匹配", $"文件{filePath}列{colName}行{row}类型为{actualType}，预期为{expectedTypes[colName]}", string.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
            return;
        }
    }
}

指定列非空验证

针对必填列，遍历所有行（或抽样）检查空值：

var requiredColumns = (string[])Dts.Variables["@RequiredColumns"].Value;
foreach (var colName in requiredColumns)
{
    int colIndex = actualColumns.IndexOf(colName) + 1;
    for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
    {
        if (string.IsNullOrWhiteSpace(worksheet.Cells[row, colIndex].Text))
        {
            Dts.Events.FireError(0, "必填列空值", $"文件{filePath}列{colName}行{row}为空值", string.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
            return;
        }
    }
}
// 所有验证通过
Dts.TaskResult = (int)ScriptResults.Success;

3. 性能优化要点 #

• 提前终止验证：只要发现任一验证不通过，立即抛出错误并终止任务，避免无用计算
• 样本抽样验证：数据类型验证仅读取前N行，大幅减少大文件的读取耗时（若业务要求100%精确，可改为全表扫描）
• 高效库选择：EPPlus相比OLEDB更灵活，无需配置DSN，读取元数据和单元格内容的性能更优

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