使用SSIS将SQL查询导出到Excel兼容XML文档指定位置的实现方法

核心思路 #

SSIS原生Excel目标组件不支持SpreadsheetML格式（也就是你说的符合Excel规范的XML格式文件），所以我们不走原生组件，通过脚本任务+Open XML SDK的方式实现写入指定工作表指定行的需求，不会破坏原有文件的多工作表结构和格式。

具体实现步骤 #

• 第一步：准备依赖
  开发环境先安装Open XML SDK 2.5，在SSIS脚本项目中添加DocumentFormat.OpenXml和WindowsBase两个程序集引用，注意把两个引用的「复制本地」属性设为True，避免部署后找不到程序集的问题。
• 第二步：配置SSIS变量
  提前在SSIS包中创建可配置变量，后续修改参数不用调整脚本代码：
  • XmlExcelFilePath：目标XML格式Excel文件的完整路径
  • TargetSheetName：要写入的目标工作表名称
  • TargetStartRow：要写入的起始行号
  • SqlQuery：需要执行的SQL查询语句
  • SqlConnStr：SQL数据库连接字符串
• 第三步：编写脚本任务核心逻辑
  新增脚本任务，选择上述变量为只读变量，编写C#代码如下：

  using DocumentFormat.OpenXml;
  using DocumentFormat.OpenXml.Packaging;
  using DocumentFormat.OpenXml.Spreadsheet;
  using System.Data;
  using System.Data.SqlClient;
  
  public void Main()
  {
      // 读取SSIS变量配置
      string filePath = Dts.Variables["User::XmlExcelFilePath"].Value.ToString();
      string sheetName = Dts.Variables["User::TargetSheetName"].Value.ToString();
      int startRow = int.Parse(Dts.Variables["User::TargetStartRow"].Value.ToString());
      string sqlConnStr = Dts.Variables["User::SqlConnStr"].Value.ToString();
      string sqlQuery = Dts.Variables["User::SqlQuery"].Value.ToString();
  
      // 执行SQL获取查询结果
      DataTable dt = new DataTable();
      using (SqlConnection conn = new SqlConnection(sqlConnStr))
      {
          SqlCommand cmd = new SqlCommand(sqlQuery, conn);
          conn.Open();
          dt.Load(cmd.ExecuteReader());
      }
  
      // 打开目标XML格式Excel文件
      using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, true))
      {
          WorkbookPart wbPart = doc.WorkbookPart;
          // 匹配目标工作表
          Sheet targetSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
          if (targetSheet == null) throw new Exception("未找到指定工作表");
          WorksheetPart wsPart = (WorksheetPart)wbPart.GetPartById(targetSheet.Id);
          SheetData sheetData = wsPart.Worksheet.Elements<SheetData>().First();
  
          // 从指定行开始写入数据
          int currentRow = startRow;
          foreach (DataRow dr in dt.Rows)
          {
              Row row = new Row() { RowIndex = (uint)currentRow };
              for (int col = 0; col < dt.Columns.Count; col++)
              {
                  Cell cell = new Cell();
                  cell.CellReference = GetColumnLetter(col) + currentRow;
                  cell.CellValue = new CellValue(dr[col].ToString());
                  // 可根据实际字段类型调整DataType，比如数字、日期对应不同的CellValues枚举
                  cell.DataType = CellValues.String;
                  row.Append(cell);
              }
              sheetData.Append(row);
              currentRow++;
          }
          wsPart.Worksheet.Save();
      }
      Dts.TaskResult = (int)ScriptResults.Success;
  }
  
  // 辅助方法：列索引转Excel列名（0→A、1→B以此类推）
  private string GetColumnLetter(int columnIndex)
  {
      int dividend = columnIndex + 1;
      string columnName = string.Empty;
      int modifier;
      while (dividend > 0)
      {
          modifier = (dividend - 1) % 26;
          columnName = Convert.ToChar(65 + modifier).ToString() + columnName;
          dividend = (int)((dividend - modifier) / 26);
      }
      return columnName;
  }
  

• 第四步：测试验证
  执行包后直接打开目标XML文件，确认数据写入位置正确，其他工作表内容和原有格式不受影响即可。

备选轻量方案（适合小数据量场景） #

如果不想编写代码，可以先把SQL查询结果导出为临时CSV文件，再添加执行进程任务调用VBS脚本，调用Excel程序打开XML格式文件，把CSV数据写入指定位置后保存。该方案需要服务器安装Excel并配置DCOM权限，稳定性低于Open XML SDK方案

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