如何使用每日生成的Excel文件持续更新SQL Server数据库

你之前用的手动导入向导本质是临时生成SSIS包执行导入，完全可以基于这套逻辑做自动化，根本不需要汇总多日数据到单个Excel，直接绕开单工作表行数限制，下面是两个可直接落地的方案：

方案1：SSIS 自动定时导入（最适配你现有操作习惯，稳定性最高） #

这个方案和你之前手动导入的逻辑完全一致，只是把手动操作改成自动执行，配置一次后长期不用管：

• 重新走一次你熟悉的导入向导流程，到最后一步的时候，勾选保存SSIS包选项，把包存到本地或者SQL Server实例上，不用每次重新配置字段映射
• （可选）如果不想每次改文件名，可以给包加个Foreach文件循环容器，设置遍历你固定存放每日Excel的文件夹，自动识别当日新增的xlsx文件，不用手动修改文件路径配置
• 配置Excel源的读取规则，加非空值过滤，自动跳过空行，保证每次只导入有效数据（也就是你说的3500-4000行有效内容，不会把空白行导进去）
• 确认目标组件的写入模式为追加写入，不要选覆盖表数据，保证新数据直接加到表现有数据末尾
• 把部署好的SSIS包挂到SQL Server代理作业上，设置每日固定时间点执行（选在你每日Excel文件更新完成后的时间即可），作业可以加执行日志记录，导入失败自动告警。

这个方案是微软官方原生支持的导入能力，稳定性强，你完全不需要维护汇总Excel，每日只需要把产出的Excel丢到指定文件夹就行，历史Excel可以定期归档到其他存储路径，不会碰到1048576行的单表行数上限问题。

方案2：T-SQL 轻量导入（不需要额外装开发工具，配置最快） #

如果不想折腾SSIS组件，可以直接用SQL Server自带的分布式查询功能直接读Excel写入表，配置步骤更简单：

• 先开启实例的临时分布式查询权限，在SSMS里执行下面的配置命令：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

• 安装和你Office版本、SQL Server实例位数匹配的Access Database Engine驱动，之后就可以直接用SQL语句读取Excel内容追加到目标表，基础语句示例：

-- 注意把表名、字段名、文件路径、工作表名替换成你自己的实际内容
INSERT INTO 你的业务目标表 (字段1,字段2,字段3,导入日期)
SELECT 字段1,字段2,字段3,GETDATE()
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\每日数据存放文件夹\当日业务数据.xlsx',
    'SELECT * FROM [数据工作表$] WHERE 主键列 IS NOT NULL'
);

• 把上面的语句改成动态拼接路径的版本，自动匹配当日命名的Excel文件，放到SQL Server代理的作业步骤里，设置每日定时执行就行，整个配置过程10分钟就能搞定。

落地注意事项 #

• 不管选哪个方案，都建议给目标表加个导入批次日期字段，每次导入的时候把执行日期写入这个字段，后续如果某一天的数据导错了，可以直接按日期删除对应批次的数据重导，不用全表回滚
• 可以给导入步骤加行数校验，比如每次导入完成后判断本次插入行数是不是在3500-4000区间，如果不在就触发告警，避免文件格式错、导空数据的问题
• 历史Excel文件不需要一直放在导入文件夹里，导入完成后可以移到归档目录，不会影响后续导入任务执行

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