SSIS脚本任务：使用SqlBulkCopy为数据表添加Filename和Sheetname字段

要给目标表的末尾添加Filename和Sheetname字段并正确填充，你需要先给从Excel读取的数据补上这两个字段的值，再执行批量导入。原代码直接用OleDbDataReader导入的话，因为数据源没有这两个字段，会导致目标表的对应字段无法被赋值（或抛出字段不匹配的错误）。

以下是修正并实现需求的完整代码：

FileInfo[] files = directory.GetFiles("*.*", SearchOption.AllDirectories);

foreach (FileInfo file in files)
{
    string directoryname = file.Directory.Name;
    string filename = file.Name;

    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFolderPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\""; 
 
    using (OleDbConnection cnn = new OleDbConnection(connectionString))
    {
        cnn.Open();

        DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        foreach (DataRow drSheet in dtSheet.Rows)
        {
            string sheetname = drSheet["TABLE_NAME"].ToString();
            // 跳过Excel系统内置工作表
            if (sheetname.Contains("xlnm"))
                continue;
     
            OleDbCommand cmd = new OleDbCommand($"select * from [{sheetname}]", cnn);
            cmd.CommandTimeout = 0;
     
            DataTable dtData = new DataTable();
            using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
            {
                adp.Fill(dtData);
            }

            // 在DataTable末尾添加Filename和Sheetname列
            dtData.Columns.Add("Filename", typeof(string));
            dtData.Columns.Add("Sheetname", typeof(string));

            // 为每一行填充对应的文件名和工作表名
            foreach (DataRow row in dtData.Rows)
            {
                row["Filename"] = filename;
                row["Sheetname"] = sheetname;
            }

            // 执行SQL批量导入
            using (SqlBulkCopy dbc = new SqlBulkCopy(myadoconn))
            {
                dbc.BatchSize = 5000;
                dbc.DestinationTableName = "Table1";
                // 若Excel列名与目标表列名不一致，需手动添加映射：
                // dbc.ColumnMappings.Add("Excel列名", "目标表列名");
                dbc.WriteToServer(dtData);
            }
        }
    }
}

关键修改说明 #

• 用using包裹OleDbConnection，确保数据库连接自动释放，避免资源泄漏
• 将Excel数据读取到DataTable，方便新增列和批量赋值
• 新增Filename和Sheetname列后，遍历所有行填充当前文件和工作表的名称
• 修正了原代码中的拼写错误（如Batchsize→BatchSize、Opne→Open）
• 跳过包含xlnm的系统工作表，避免读取无效数据

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