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.

migrated from stackoverflow.com Jul 1 '14 at 14:10

This question came from our site for professional and enthusiast programmers.

  • You should provide your code here... – Eliyahu Jul 1 '14 at 11:10
  • What are the data types of these six fields? Are they all DATE? – Jon of All Trades Jul 1 '14 at 14:15
  • all are date.. issue is due to date stored in different formats in Oracle, hence conversions failed. – HappieFeet Jul 3 '14 at 10:45
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.

  • Thanks for your advice. I found the issue. It was due to different date formats being used in Oracle data. The year part was yyyy for some dates, while just yy for other dates. So for eg: when a user enters '98 as the year, the system saved it as 0098 rather than 1998. Hence, the data was not processed at oledb source itself in bids. – HappieFeet Jul 3 '14 at 10:44
  • Please don't add "thank you" as an answer. Once you have sufficient reputation, you will be able to vote up questions and answers that you found helpful. – Colin 't Hart Aug 5 '14 at 14:53
  • @user3793534 when the answer proves to be your solution, you should check the checkmark underneath the votes on the left-hand side. We can provide further details if you need more assistance. Thank you. – jcolebrand Aug 5 '14 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.