SSIS从Excel导数时转换DT_WSTR为YYYY-MM-DD出现月日错位问题

问题触发的根本原因 #

• 从Excel输出的日期列本身为字符串类型（DT_WSTR）时，SSIS内置数据转换组件做DT_WSTR到DT_TIMESTAMP的类型转换时，不会主动识别Excel内的实际日期展示格式，只会严格按照当前组件/包配置的系统区域日期规则解析字符串
• 出现“部分行值正确、部分行月日互换”的现象，本质是解析歧义导致的：
  • 当原始字符串中的“日”数值大于12时，SSIS识别到该值不可能为月份，会直接按数值位置对应正确的年月日，转换后的值和Excel原始值完全一致
  • 当原始字符串中的“日”数值小于等于12时，SSIS无法区分该位置是月份还是日期，就会按绑定的区域规则匹配，比如绑定en-US区域时就把前两位当月份、中间两位当日，原本的日/月顺序的字符串就会被转成月/日顺序，最终出现1月数据里混进了2月、3月日期的错位问题
• 对应异常的存储效果可参考截图：
  

可直接落地的修复方案 #

• 彻底绕开依赖隐式区域解析的内置数据转换流程
  1. 在Excel源组件配置中，将目标日期列的输出类型强制设置为DT_WSTR，禁止Excel源在读取阶段提前做隐式日期格式转换
  2. 替换原有的数据转换组件，选用脚本组件做显式格式解析：通过编写自定义解析逻辑，匹配Excel内实际的固定日期格式，彻底脱离系统区域设置的影响，示例代码如下：

     // 脚本组件中处理逐行日期转换
     string rawDateStr = Row.RawDateString.Trim();
     // 按Excel实际的存储格式指定匹配规则，示例匹配格式为yyyy/M/d
     Row.ConvertedTimestamp = DateTime.ParseExact(
         rawDateStr, 
         "yyyy/M/d", 
         System.Globalization.CultureInfo.InvariantCulture
     );
     

• 不想写脚本的轻量化替代方案
  • 逐一检查包级、数据流任务级、Excel源组件、原数据转换组件的LocaleID属性，把所有和日期解析相关的组件区域设置，调整为和Excel内日期格式匹配的区域：比如Excel内的日期展示是日/月/年顺序，就把组件LocaleID设为English (United Kingdom)；如果是月/日/年就设为English (United States)，不要默认继承服务器的系统区域配置
  • 调整完成后重新跑批验证，注意要先把之前已经错位导入的脏数据做清洗修正

常见避坑提醒 #

• 不要试图通过更换DT_DBDATE、DT_DATE等其他日期类型解决问题，只要转换过程没有做明确的格式绑定，遇到歧义字符串就会出现同样的月日互换问题
• 不要依赖SQL端的后续字段格式转换纠错，SSIS阶段已经把字符串转成了错位的日期值，再到SQL端的转换已经丢失了原始的字符串信息，无法还原正确值

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