SQL Server SSIS : ADO.net date issue

Question

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.

Please post the table layouts for the tables in Oracle and SQL Server. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 20, 2012 at 19:06
Stays Table structure : pt_id|admission_date, Sample Data (123456|01-MAR-99,123457|01-MAR-99) that is my table structure in Oracle — user1141584
– user1141584, Commented Jul 20, 2012 at 19:10

Community · Accepted Answer · 2017-05-23 12:30:58Z

3

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

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Jul 20, 2012 at 19:32

William Salzman

6,4463 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1141584 · Accepted Answer · 2012-07-20 19:32:13Z

0

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.

answered Jul 20, 2012 at 19:32

user1141584

6195 gold badges18 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL Server SSIS : ADO.net date issue

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related