CODEWITHSUNDEEP
sql-serveroracle-databasesql-server-2008ssis
user1141584
user1141584

Reputation: 619

SQL Server SSIS : ADO.net date issue

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.

Below is my query in Oracle.

select pt_id,admission_date 
from stays

It works fine.

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

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

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.

Upvotes: 1

Views: 7114

Answers (2)

William Salzman
William Salzman

Reputation: 6446

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.

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.

See Dealing With Timestamp/Datetime when copying from Oracle to Sql Server using SSIS

And Oracle to SQL2005 DATETIME field overflows in SSIS

Upvotes: 3

user1141584
user1141584

Reputation: 619

Well, the issue was the Oracle table was a view and not a table , the admission_date was extracted from the decode command for SQL. I pulled as it is and it is resolved my issue.

Upvotes: 0

Related Questions