Date time from Oracle to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Date time from Oracle to SQL

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2011-12-12 : 11:07:42

I am using ssis to get data from oracle to sql and am getting the following error....

Coming from Oracle column: EX_DATE....database timestamp [DT_DBTIMESTAMP]
Into SQL 2005 column: EX_DATE....(datetime,null)

Message
Executed as user: me\too. ...000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:37:00 AM Error: 2011-12-12 08:38:27.65 Code: 0xC0202009
Source: Tab1_T OLE DB Destination [16] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". End Error Error: 2011-12-12 08:38:27.67 Code: 0xC020901C
Source: Tab1_T OLE DB Destination [16] Description: There was an error with input
column "EX_Date" (153) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.". End Error Error: 2011-12-12 08:38:27.67 Code: 0xC0209029
Source: Tab1_T OLE DB Destination [1... The package execution fa... The step failed.

Any help will be much appreciated...
r/p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 11:14:07

did you try DT_DBDATE ? does values have timepart also?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

poser
Posting Yak Master

124 Posts

Posted - 2011-12-12 : 13:00:08

Date in Oracle:
10-Aug-2011 12:00:00 PM

SQL:
2014-08-31 00:00:00.000

Thanks,
r/p

poser
Posting Yak Master

124 Posts

Posted - 2011-12-13 : 10:11:22

Is the problem it can't format it into what sql wants?
r/p

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-12-13 : 12:47:30

You could try converting the Oracle date to an ISO string.

In Oracle, I think this should be something like:

TO_CHAR(EX_DATE, 'YYYYMMDD HH:MI:SS')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 13:36:00

see why iso format is recommended for passing date values in sql server

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources