Conversion Of Oracle Date Time To Sql Server Date Time in SSIS

This is driving me nuts..

I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.

I'm struggling with this for a quite a while and I'm not able to get it working.

I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511 23:06:30:000

the space in MM : SS is intentional here, since without that space it appread as smiley

I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data

I'm struck with error for hours now. Any pointers would be helpful.

Thanks

Any idea why this simple straight forward string to date time conversion keeps failing with the error message, conversion failed due to potential loss of data?

The input values looks like this 20070511 23:06:30, what is that I'm missing here for the conversion to fail?

Thanks

|||

As much as it sounds ridiculous, looks like SSIS does not understand YYYYMMDD format...

Thanks to Jamie Thomson' s post here, which solved the problem.

http://blogs.conchango.com/jamiethomson/archive/2006/06/26/SSIS_3A00_-Parsing-datetime-values.aspx