SSIS 检查Excel文件重复值 触发报错并禁止数据插入数据库

1. 前期准备 #

• 明确Excel中判断重复的业务主键（单字段/多字段联合均可），确定校验规则
• 提前配置SMTP发送邮件的账号权限，确保SSIS服务所在服务器可正常连通邮件服务器
• 安装对应版本的Excel驱动，注意32/64位驱动要和SSIS运行环境匹配

2. 控制流核心配置 #

首先创建Excel连接管理器，配置好待加载Excel文件的路径、版本、对应Sheet信息。

2.1 配置重复值校验任务 #

添加名称为重复值校验任务的数据流任务，进入任务内部配置：

• 拖入Excel源组件，关联提前创建的Excel连接管理器，读取待校验的全部业务数据
• 拖入聚合转换组件，和Excel源连线，配置聚合规则：选中所有用于判断重复的业务字段，操作设置为「分组依据」；新增一列重复计数，操作设置为「计数」
• 拖入条件拆分转换组件，和聚合转换连线，新增拆分规则：重复计数 > 1，对应输出命名为重复数据分支
• 拖入行计数转换组件，和重复数据分支连线，提前创建Int类型的包变量@[User::重复行数]，将行计数结果赋值给该变量

2.2 配置分支流转规则 #

回到控制流页面，配置两个优先约束：

• 合法数据分支：连接「重复值校验任务」和后续入库任务，约束类型选「表达式和约束」，值为「成功」，表达式设置为@[User::重复行数] == 0，仅满足该条件时才执行入库逻辑
• 异常处理分支：连接「重复值校验任务」和异常处理节点，约束类型选「表达式和约束」，值为「成功」，表达式设置为@[User::重复行数] > 0，仅满足该条件时执行异常逻辑

2.3 配置业务逻辑节点 #

• 合法分支添加名称为合法数据入库任务的数据流任务，内部配置Excel源到数据库目标表的字段映射即可，所有数据无重复才会执行该步骤，不会出现部分数据写入的问题
• 异常分支依次添加两个组件：
  1. 脚本任务：将属性FailPackageOnFailure设置为True，执行时直接抛出错误终止整个包运行
  2. 发送邮件任务：提前配置SMTP连接管理器，设置收发件人、邮件主题、正文，可将@[User::重复行数]变量拼入正文，告知相关人员重复数据条数

3. 可选优化项 #

• 如需留存重复值明细供核查，可在重复数据分支新增平面文件目标组件，将重复记录导出为日志文件，作为邮件附件发送
• 可开启包的事务配置，将整个流程纳入同一个事务，进一步保证数据一致性
• 调试阶段可将组件DelayValidation属性设置为True，避免设计阶段的不必要验证报错

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