表达式:

(DT_DATE)("20" + SUBSTRING([ReceivedDt], 1, 2) + "-" + SUBSTRING([ReceivedDt], 3, 2) + "-" + SUBSTRING([ReceivedDt], 5, 2))

为了可读性:

(DT_DATE)("20" + 
    SUBSTRING([ReceivedDt], 1, 2)  + "-" + 
    SUBSTRING([ReceivedDt], 3, 2)  + "-" +
    SUBSTRING([ReceivedDt], 5, 2))

问题原因:

您不能将 YYMMDD 格式的字符串转换为有效的日期值.使用上述表达式在值前加上 20 将值转换为可以转换为的格式 YYYYMMDD日期.

使用OLE DB 源、派生列转换和两个多播转换来配置数据流任务.

Configure the Data Flow Task with a OLE DB Source, Derived Column Transformation and two Multicast Transformations.

使用以下具有 YYMMDD 格式的值的查询配置 OLE DB 源.

Configure the OLE DB Source with the following query that has the values in format YYMMDD.

SELECT  '120304' AS ReceivedDt UNION
SELECT  '120107' AS ReceivedDt UNION
SELECT  '121211' AS ReceivedDt UNION
SELECT  '121312' AS ReceivedDt;

配置派生列转换，将ReceivedDt列中的传入值以YYMMDD格式转换为ReceivedDate 格式 YYYYMMDD.

Configure the Derived Column Transformation to convert the incoming value in column ReceivedDt in format YYMMDD to ReceivedDate of format YYYYMMDD.

在派生列转换和多播转换之间附加数据查看器.将错误输出配置为 Redirect row 而不是 Fail 组件.

Attach data viewer between the Derived Column Transformation and the Multicast Transformations. Configure the Error Output to Redirect row instead of Fail component.

执行包将显示可以在派生列转换和成功多播转换之间的数据查看器中转换为有效日期值的字符串值.

Executing the package will display the string values that could be converted to valid date values in the data viewer between Derived Column Transformation and the success Multicast Transformation.

执行包将在派生列转换和错误多播转换之间显示无法在数据查看器中转换为的字符串值.

Executing the package will display the string values that could not be converted to in the data viewer between Derived Column Transformation and the error Multicast Transformation.