SQL Server SSIS:ADO.net 日期问题 [英] SQL Server SSIS : ADO.net date issue

问题描述

我创建了一个 SSIS 包，它从 Oracle 10g 服务器中提取数据并将它们推送到 SQL Server 2008 中.我在 Oracle 中有名为 admission_date 的日期数据类型字段.

I have created an SSIS package that pulls the data from Oracle 10g server and pushes them in SQL Server 2008. I have date datatype field in Oracle named admission_date.

以下是我在 Oracle 中的查询.

Below is my query in Oracle.

select pt_id,admission_date 
from stays

效果很好.

当我尝试通过 SSIS 包通过 ADO.net 在 SQL Server 中提取数据时.我收到以下错误消息.

When I tried to pull the data in SQL Server through ADO.net via an SSIS package. I get the below error messages.

错误:STAYS 处的 0xC02090F5，ADO NET 源 [1050]:组件ADONET Source" (1050) 无法处理数据.ORA-01843: not a有效月份

Error: 0xC02090F5 at STAYS, ADO NET Source [1050]: The component "ADO NET Source" (1050) was unable to process the data. ORA-01843: not a valid month

错误: 0xC0047038 at STAYS, SSIS.Pipeline: SSIS 错误代码DTS_E_PRIMEOUTPUFAILED.组件ADO NET"上的 PrimeOutput 方法Source" (1050) 返回错误代码 0xC02090F5.组件返回管道引擎调用 PrimeOutput() 时的故障代码.这故障代码的含义由组件定义，但错误是致命的，管道停止执行.可能有错误在此之前发布的消息包含有关失败的更多信息.

Error: 0xC0047038 at STAYS, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "ADO NET Source" (1050) returned error code 0xC02090F5. 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.

对此的任何意见都会有所帮助.

Any input on this would helpful.

推荐答案

使用像这样的 TO_CHAR(addmission_date,'yyyy-mm-dd HH24:MI:SS') 将您的 oracle 日期格式化为 SQL 喜欢的格式.

Use something like this TO_CHAR(addmission_date,'yyyy-mm-dd HH24:MI:SS') to format your oracle date into a format that SQL likes.

另请注意，Oracle 的有效日期范围比 SQL 大得多，因此根据您的 Oracle 数据，您可能有有效的 oracle 日期，但不会被视为有效的 SQL 日期.

Also be aware that Oracle has a much larger valid date range than SQL so depending upon your Oracle data you may have valid oracle dates that don't come across as valid SQL dates.

请参阅使用 SSIS 从 Oracle 复制到 Sql Server 时处理时间戳/日期时间

和 SSIS 中 Oracle 到 SQL2005 DATETIME 字段溢出

这篇关于SQL Server SSIS:ADO.net 日期问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！