SSIS中AS400数值型日期字段转SQL Server日期类型失败求助

你遇到的这个报错，核心原因大概率是AS400数据源里存在不符合YYYYMMDD规则的无效日期值，这些异常值在转换时触发了类型溢出。我给你拆解几个可行的解决方案：

第一步：先排查AS400中的无效数据 #

首先得定位问题数据，看看你的Decimal(8)字段里有没有诸如00000000、99999999，或者月份为13、日期为32这类明显不合法的数值。在AS400上跑这个查询就能找出异常记录：

SELECT myfield
FROM yourAS400Table
WHERE 
    -- 检查年份是否在合理范围（比如1900-2100）
    SUBSTRING(CHAR(myfield), 1, 4) NOT BETWEEN '1900' AND '2100'
    -- 检查月份是否在1-12之间
    OR SUBSTRING(CHAR(myfield), 5, 2) NOT BETWEEN '01' AND '12'
    -- 先粗略检查日期范围，更精确的可以用VALID_DATE函数
    OR SUBSTRING(CHAR(myfield), 7, 2) NOT BETWEEN '01' AND '31'
    -- 用AS400自带的VALID_DATE直接验证日期合法性
    OR NOT VALID_DATE(SUBSTRING(CHAR(myfield),1,4) || '-' || SUBSTRING(CHAR(myfield),5,2) || '-' || SUBSTRING(CHAR(myfield),7,2))

找到这些无效值后，要么从业务层面修正数据，要么在转换时过滤掉或替换为NULL。

第二步：优化AS400端的转换逻辑 #

在AS400的查询里直接处理无效值，只返回合法的日期，从源头避免SSIS报错：

CASE 
    -- 先验证日期合法性
    WHEN VALID_DATE(SUBSTRING(CHAR(myfield),1,4) || '-' || SUBSTRING(CHAR(myfield),5,2) || '-' || SUBSTRING(CHAR(myfield),7,2))
    -- 合法则转换为Date类型
    THEN DATE(SUBSTRING(CHAR(myfield),1,4) || '-' || SUBSTRING(CHAR(myfield),5,2) || '-' || SUBSTRING(CHAR(myfield),7,2))
    -- 非法则返回NULL（也可以替换成你指定的默认日期，比如'1900-01-01'）
    ELSE NULL 
END AS converted_date

这里要注意：必须用CHAR(myfield)把Decimal字段转成字符串再截取，直接对数值字段用substring可能会出现隐式转换的问题。

第三步：SSIS端的兜底处理 #

如果还是需要在SSIS里完成转换，可以按以下步骤调整：

1. 先把Decimal(8)字段转成字符串类型（比如DT_STR(8, 1252)），避免数值直接转日期的溢出风险。
2. 使用派生列组件添加转换逻辑，同时处理无效值：
   派生列表达式示例：

   LEN(myfield_str) == 8 && ISDATE(SUBSTRING(myfield_str,1,4) + "-" + SUBSTRING(myfield_str,5,2) + "-" + SUBSTRING(myfield_str,7,2)) 
   ? (DT_DATE)(SUBSTRING(myfield_str,1,4) + "-" + SUBSTRING(myfield_str,5,2) + "-" + SUBSTRING(myfield_str,7,2)) 
   : NULL(DT_DATE)
   

3. 开启错误输出：在数据转换/派生列组件上配置错误输出，把转换失败的记录路由到单独的输出文件，方便后续排查具体问题数据，而不是让整个包直接失败。

为什么之前的方法会报错？ #

你之前尝试的几种转换逻辑本身是正确的，但如果数据源里存在无效日期（比如20181301，月份为13），不管是AS400的DATE()函数还是SSIS的类型转换，都会因为无法解析成合法日期而触发“溢出”错误——本质是无效值导致的转换失败，不是语法问题。

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