plsql - SSIS - 将 Oracle 日期转换为 SQL 日期时间

Question

甲骨文 11gSQL 服务器 2008 R2

尝试将数据从 OLE DB 源 (Oracle DB) 移动到 OLE DB 目标 (SQL Server) 时，我收到以下错误。在出现错误之前，已将 65000 条记录中的 8922 条添加到 SQL 服务器。 8922 的相关日期列确实在应该添加的时候添加了日期。 Oracle 上的列是 DATE 类型。 SQL Server 2008 上的列是日期时间。

On the SQL Server, for record # 8922 the RSVP_END_DT = 2007-06-25 12:06:00.000
On the Oracle Server for record # 8922 the RSVP_END_DT = 25-JUN-07
On the Oracle Server, for what should be record # 8923 in the SQL DB, the RSVP_END_DT = 10-AUG-07

我已经搜索了每个“DTE_E”错误，但没有找到帮助。我还尝试过使用数据转换并编写源 SQL 以使用正确的 SQL 格式将日期设置为“to_char”，但这也不起作用。还有其他建议吗？

[OLE DB Destination [424]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [424]] Error: There was an error with input column "RSVP_END_DT" (487) on input "OLE DB Destination Input" (437). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

[OLE DB Destination [424]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (437)" failed because error code 0xC020907A occurred, and the error row disposition on "input "OLE DB Destination Input" (437)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (424) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (437). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

[OLE_DB_SOURCE[1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "OLE_DB_SOURCE" (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

Answer 1

这是我之前关于这个话题的三个回答。您很可能超出了带有该错误消息的 Sql Server 日期范围。我在上一个答案中解释了 Datetime 和 datetime2 的不同限制。

https://stackoverflow.com/a/11585853/236348

https://stackoverflow.com/a/2231164/236348

https://stackoverflow.com/a/11229159/236348