2

I am using BIDS to export data from Oracle to SQL server.

I am using OLEDB source and destination.

I have around 6 datetime fields in ORACLE out of which two fields give no problem while 4 fields give invalid date format issues.

These four fields either have nulls or have only date part in the datetime fields of ORACLE.

Any solution for this?

I have Tried all sorts of data conversions and insert nulls option.

Hector
  • 1,042
  • 1
  • 8
  • 22
HappieFeet
  • 23
  • 1
  • 6

1 Answers1

1

You need to use derived column transformation and with help of expressions perform required conversion operation on original columns.

To replace null with default value use REPLACENULL(expression 1,expression 2) in derived column transformation.

REPLACENULL returns the value of second expression parameter if the value of first expression parameter is NULL; otherwise, returns the value of first expression.

REPLACENULL(SHIPPED_DATE, "1900-01-01")

Or with ISNULL

(DT_DBTIMESTAMP) (ISNULL(SHIPPED_DATE) ? “1900-01-01” : SHIPPED_DATE) 

ISNULL([SHIPPED_DATE]) ? "UnknownDate" : [SHIPPED_DATE]

To deal with date part, you can use Concatenate and make into valid date-time format.

Say you have 01/07/2014 as original field value then make it to "01/07/2014 00:00:00"

OracleDateField + "00:00:00"

If you have to perform both operations at same time(checking nulls and dealing with date part ) then it's good to use script component rather than derived column transformation.

Vikramsinh Shinde
  • 126
  • 3