Azure Data Factory字符串转DateTime错误[类型转换设置]

问题背景 #

• CSV文件中DATE列格式为dd/MM/yyyy（示例：28/10/2024），需转换为SQL Server表的DateTime类型，已完成列映射
• 初始类型转换设置时触发以下错误：

ErrorCode=TypeConversionFailure,Exception occurred when converting value '28/10/2024' for column name 'DATE' from type 'String' (precision:, scale:) to type 'DateTime' (precision:255, scale:255). Additional info: String was not recognized as a valid DateTime.

• 为DATE列指定DateTime format为dd/MM/yyyy后，又出现LoadDate列格式错误，该列格式为yyyy-MM-dd HH:mm:ss.fff

解决方案 #

1. 为不同日期列单独配置转换格式 #

ADF的全局日期格式设置会作用于所有日期类型列，导致格式不匹配的列报错，必须针对每个列单独设置：

• 找到DATE列的类型转换规则，设置Type为DateTime，DateTime format为dd/MM/yyyy
• 找到LoadDate列的类型转换规则，设置Type为DateTime，DateTime format为yyyy-MM-dd HH:mm:ss.fff
  确保每个列的格式配置与其实际数据格式完全匹配，不要使用全局统一格式。

2. 检查CSV数据集的列级别格式覆盖 #

在CSV数据集的配置中，可以针对单个列设置格式，避免全局设置冲突：

• 进入CSV数据集的Schema选项卡
• 编辑DATE列，设置Type为DateTime，并指定DateTime format为dd/MM/yyyy
• 对LoadDate列执行相同操作，设置对应格式yyyy-MM-dd HH:mm:ss.fff

3. 验证数据格式一致性 #

确认CSV文件中：

• DATE列所有值严格遵循dd/MM/yyyy格式，无诸如MM/dd/yyyy或缺失分隔符的脏数据
• LoadDate列所有值严格遵循yyyy-MM-dd HH:mm:ss.fff格式，无格式异常值

4. 使用派生列预处理日期（兜底方案） #

如果上述配置仍无法解决问题，可在数据流中添加派生列活动手动转换：

• 针对DATE列，使用表达式：toDate(DATE, 'dd/MM/yyyy')
• 针对LoadDate列，使用表达式：toTimestamp(LoadDate, 'yyyy-MM-dd HH:mm:ss.SSS')
  将派生后的列映射到SQL Server表的对应DateTime列即可。

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