SSIS通过ODBC运行作业时报错invalid character value for cast specification

根因分析 #

你遇到的四类报错核心是SSIS的ODBC驱动对数据类型转换、长度、精度的校验规则远严格于SQL Server原生执行逻辑：

• 22018错误是类型转换不匹配
• 01004是字符串长度超出目标字段定义
• 01S07是时间精度截断触发的校验失败
  单独这一个字段报错是因为该字段存在超出常规datetime范围的异常值，或者SSIS自动推断的元数据与实际输出不匹配。

解决方案 #

• 第一步：排查隐藏异常值
  执行以下SQL找出不符合转换规则的记录，你之前确认无空值不代表无边界异常值：

SELECT PurcDate 
FROM 你的表名 a 
WHERE ISDATE(a.PurcDate) = 0 
OR YEAR(a.PurcDate) < 1900 
OR YEAR(a.PurcDate) > 9999
OR LEN(CONVERT(varchar(10), a.PurcDate, 20)) > 10

常见异常值包括datetime2类型的超范围值、datetimeoffset带时区的精度值，原生SQL执行会自动兼容，但ODBC驱动会直接抛出错误。

• 第二步：修改转换代码适配ODBC规则
  不要先转varchar再隐式转nvarchar，转换时直接指定为nvarchar类型，同时增加容错处理，另外不要给别名加单引号，避免SSIS元数据识别错误：

-- 支持SQL Server 2012及以上版本，容错性最高，异常值返回空
TRY_CONVERT(nvarchar(10), a.PurcDate, 20) AS PurcDate

-- 低版本SQL Server兼容写法，异常值返回默认日期
CASE WHEN ISDATE(a.PurcDate) = 1 THEN CONVERT(nvarchar(10), a.PurcDate, 20) ELSE N'1900-01-01' END AS PurcDate

-- 极致规避精度问题写法：先转DATE去掉时间部分再转字符串
CONVERT(nvarchar(10), CAST(a.PurcDate AS DATE), 20) AS PurcDate

• 第三步：手动修正SSIS ODBC源元数据
  打开ODBC源的高级编辑器，找到输出列中的PurcDate字段，确认数据类型为Unicode字符串[DT_WSTR]，长度强制设为10，不要使用自动推断的默认值，自动推断经常会误判长度导致截断报错。如果有条件可以将ODBC Driver 17 for SQL Server升级到最新版本，旧版本存在已知的datetime转换bug。

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