SSIS Cast String to Database Date 转换失败

2026-10-24数据小许

结论

表达式失败，是因为它实际生成的是：

2020-OCT-04

而不是：

2020-10-04

DT_DBDATE 能否识别英文月份缩写 OCT，取决于运行环境的区域设置和日期解析规则。表达式即使以前能正常工作，也可能在服务器迁移、执行账户变更或 Windows 区域设置调整后，无法再识别 2020-OCT-04。

SSIS 还会处理 Excel 中的所有输入行。只要有一行为空、含有不可见字符、格式不同或日期无效，并且错误处置方式设为“失败组件”，整个 Derived Column 就会报错。因此，报错不一定是由 OCT 04 2020 这一行引起的。

先确认表达式的实际结果

原表达式的三个部分分别是：

SUBSTRING([BILLING CYCLE DATE],8,4)  -> 2020
SUBSTRING([BILLING CYCLE DATE],1,3)  -> OCT
SUBSTRING([BILLING CYCLE DATE],5,2)  -> 04

连接后得到：

2020-OCT-04

也就是说，原表达式只调整了年、月、日的位置，并没有把 OCT 转换成数字月份 10。

使用数字月份进行转换

如果输入始终严格采用 MMM dd yyyy 格式，可以先把月份缩写映射为数字，再转换为 DT_DBDATE：

(DT_DBDATE)(
    SUBSTRING(TRIM([BILLING CYCLE DATE]),8,4)
    + "-"
    + (
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "JAN" ? "01" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "FEB" ? "02" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "MAR" ? "03" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "APR" ? "04" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "MAY" ? "05" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "JUN" ? "06" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "JUL" ? "07" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "AUG" ? "08" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "SEP" ? "09" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "OCT" ? "10" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "NOV" ? "11" :
        UPPER(SUBSTRING(TRIM([BILLING CYCLE DATE]),1,3)) == "DEC" ? "12" :
        "00"
    )
    + "-"
    + SUBSTRING(TRIM([BILLING CYCLE DATE]),5,2)
)

输入为：

OCT 04 2020

中间生成的字符串就是：

2020-10-04

随后再把它转换为 DT_DBDATE，就不必依赖系统对英文月份名称的识别能力。

但这段表达式遇到非法月份时会生成 00，最后仍会发生转换错误。实际流程中应先验证输入，再进行类型转换。

更稳妥的处理流程

可以使用两个 Derived Column，或配合 Conditional Split 分步处理：

使用 TRIM 清除首尾空格。
检查字段是否为 NULL 或空字符串。
检查字符串长度是否至少为 11。
检查月份缩写是否属于 JAN 到 DEC。
构造 yyyy-MM-dd 字符串。
将字符串转换为 DT_DBDATE。
把格式不正确的行重定向到错误输出，避免整个 Data Flow 停止。

以下输入需要特别检查：

OCT 4 2020
OCT 04 2020 
Oct 04 2020
OCT 04 20
N/A
NULL
空字符串

固定位置的 SUBSTRING 只适用于严格的 MMM dd yyyy 格式。例如，OCT 4 2020 少了一个字符，后续截取位置就会全部错位。

输入格式不固定时使用 Script Component

如果 Excel 数据可能含有多余空格、大小写变化或多种日期格式，用 Script Component 会比维护很长的 Derived Column 表达式更方便。可以通过 DateTime.TryParseExact 明确按英文月份规则解析：

using System;
using System.Globalization;

string value = Row.BILLINGCYCLEDATE == null
    ? null
    : Row.BILLINGCYCLEDATE.Trim();

DateTime parsedDate;

bool success = DateTime.TryParseExact(
    value,
    "MMM dd yyyy",
    CultureInfo.InvariantCulture,
    DateTimeStyles.AllowWhiteSpaces,
    out parsedDate
);

if (success)
{
    Row.ConvertedDate = parsedDate;
}
else
{
    // 按包的设计设置为 NULL、记录原始值，或重定向错误行
    Row.ConvertedDate_IsNull = true;
}

CultureInfo.InvariantCulture 明确要求按英文月份缩写解析，不受运行 SSIS Package 的机器或账户区域设置影响。

如果日期中的日可能没有前导零，可以同时接受两种格式：

string[] formats =
{
    "MMM dd yyyy",
    "MMM d yyyy"
};

bool success = DateTime.TryParseExact(
    value,
    formats,
    CultureInfo.InvariantCulture,
    DateTimeStyles.AllowWhiteSpaces,
    out parsedDate
);

排查究竟是哪一行失败

Derived Column 报错通常只表示某一行无法转换，不会直接显示有问题的值。可以暂时采用以下方法排查：

将 Derived Column 的错误输出设置为 Redirect row。
在错误路径中保留原始 BILLING CYCLE DATE。
把错误行写入文本文件或暂存表。
检查字段长度、首尾空格和不可见字符。
确认 Excel 中是否存在空单元格、公式错误或其他日期写法。

示例值看起来正确，并不表示文件中的每一行都采用相同格式。重定向错误行后，才能看到真正导致转换失败的数据。

日期值本身没有显示格式

DT_DBDATE 是日期类型，不会保存 yyyy-MM-dd 这样的显示格式。转换成功后保存的是日期值。最终显示为：

2020-10-04

还是其他形式，由目标数据库的列类型、查询语句或下游展示工具决定。

如果目标是数据库日期列，应写入 date 类型，不要为了控制显示格式再次转换成字符串。只有最终确实需要文本输出时，才在最后一步把日期格式化为 yyyy-MM-dd。

可靠的处理方式是先把英文月份明确转换为数字，或者在 Script Component 中使用 TryParseExact 和 InvariantCulture。同时把错误行重定向出来，以便找出 Excel 中格式不正确的实际数据。