仅用SSIS与存储过程实现大表拆分导出为多个平面文件方案咨询

以下两类方案均仅涉及SSIS和存储过程，完全符合你的技术限制要求：

方案1：纯SSIS实现（无需调整数据库侧逻辑，改动成本最低） #

直接在现有SSIS包基础上调整即可，无需新增数据库对象：

• 第一步：新增变量配置
  在SSIS包中新增4个变量：
  • intBatchSize：整数类型，赋值为单批拉取行数，初始可设为1000000（后续可根据单条数据大小调整，保证单文件输出不超过4.5G即可）
  • intBatchNumber：整数类型，初始值为1，用于生成文件名后缀
  • intTotalBatch：整数类型，用于存储总分批数量
  • strOutputFileName：字符串类型，通过表达式配置为"file_"+(DT_WSTR,10)@[User::intBatchNumber]+".csv"，自动生成每批对应的输出文件名
• 第二步：配置循环逻辑
  新增执行SQL任务，查询语句写SELECT CEILING(COUNT(1)*1.0/? ) AS TotalBatch FROM 你的表名，将参数映射到intBatchSize变量，查询结果赋值给intTotalBatch变量。
  新增For循环容器，循环条件设置为@intBatchNumber <= @intTotalBatch，循环迭代规则配置为intBatchNumber每次自增1。
• 第三步：配置分批拉取数据流
  在For循环容器内添加数据流任务：
  • OLE DB源改用参数化分页查询，必须用唯一有序的字段（优先选主键/聚集索引键）排序，避免分页重复或遗漏数据：

    适配SQL Server 2012及以上版本的分页语句：

    SELECT * FROM 你的表名
    ORDER BY 主键ID
    OFFSET (@BatchNumber -1)*@BatchSize ROWS
    FETCH NEXT @BatchSize ROWS ONLY
    

    适配SQL Server 2008及以下版本的分页语句：

    SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER(ORDER BY 主键ID) AS RowNum FROM 你的表名
    ) t
    WHERE RowNum BETWEEN (@BatchNumber -1)*@BatchSize +1 AND @BatchNumber*@BatchSize
    

    把查询中的@BatchNumber映射到intBatchNumber变量、@BatchSize映射到intBatchSize变量即可。
  • 平面文件连接管理器的ConnectionString属性绑定到strOutputFileName变量，实现每批自动输出对应名称的文件。

方案2：SSIS+存储过程实现（性能更优，适合亿级以上超大数据量表） #

如果纯SSIS分页多次扫描全表性能不足，可通过存储过程一次性生成分批标记，减少表扫描次数：

• 第一步：创建分批存储过程
  数据库侧创建存储过程逻辑如下：

  CREATE PROCEDURE sp_GetExportBatchInfo
  @BatchSize INT = 1000000
  AS
  BEGIN
    -- 仅扫描一次全表，生成带分批标记的临时表
    SELECT *, 
      CEILING(ROW_NUMBER() OVER(ORDER BY 主键ID)*1.0/@BatchSize) AS BatchNum
    INTO #ExportTempData
    FROM 你的表名;
  
    -- 返回所有分批号供SSIS循环调用
    SELECT DISTINCT BatchNum FROM #ExportTempData ORDER BY BatchNum;
  END
  

• 第二步：SSIS包调整
  1. 先执行上述存储过程，将返回的分批号列表存入对象类型变量objBatchList
  2. 新增Foreach循环容器遍历objBatchList，把每次遍历到的分批号赋值给intBatchNumber变量
  3. 循环内的数据流任务，OLE DB源查询写SELECT 排除BatchNum的其他字段 FROM #ExportTempData WHERE BatchNum = ?，参数映射intBatchNumber变量，动态文件名配置和方案1完全一致。

注意事项 #

• 必须使用全局唯一、有序的字段作为分页排序依据，禁止无排序分页，否则会出现数据重复或遗漏
• 分批大小不要固定为100万，可先导出100万条测试文件大小，反推合适的分批数值，保证单文件大小稳定在4~4.8G区间，留出冗余避免超出下游5G限制
• 如果导出期间表有写入操作，建议加WITH(NOLOCK)查询或者先做表快照，避免数据变动导致分页异常

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