Dealing with Timestamp/Datetime when copying data from Oracle to SQL Server using SSIS

Question

I am using SSIS to copy data from a table in Oracle to a table in SQL Server 2005. There's no transformation required, so it's just a straight line connecting OLE DB Source to OLE DB Destination. Fairly simple and works, but during execution, a record in the Oracle table contains a timestamp value of the year 0002 in encountered, so SSIS errors out due to an oveflow exception.

If I create an extra column in SQL Server with the data type of string, and remap, then that works fine. However, I prefer to keep the Datatime column and use it in my destination. I am OK with replacing the year 0002 with something like 1900 or something like that. So what's the best way to achieve this if-then-else in SSIS?

Jondlm · Accepted Answer · 2013-11-12 19:08:50Z

7

I usually let Oracle deal with that by using something like this in my source query:

CAST( Coalesce (
CASE 
 WHEN TO_CHAR(Effective_Date,'yyyy-mm-dd HH24:MI:SS') < '1900-01-01 00:00:00' 
       THEN TO_DATE('9999-12-31 00:00:00','yyyy-mm-dd HH24:MI:SS') 
 ELSE Effective_Date 
END ,TO_DATE('9999-12-31 00:00:00','yyyy-mm-dd HH24:MI:SS')) AS DATE) AS Effective_Date

This sets a valid but very future date (dictated by the company I am on contract to as the representation of an invalid date in a required date field. You could also use '1900-01-01 00:00:00' instead of '9999-12-31 00:00:00') in cases where the original date is null or less than 1900-01-01 00:00:00. It also avoids post processing by SSIS on the date field.

edited Nov 12, 2013 at 19:08

Jondlm

8,8923 gold badges26 silver badges30 bronze badges

answered Feb 9, 2010 at 17:45

William Salzman

6,4463 gold badges35 silver badges51 bronze badges

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

4 Comments

ErikE Over a year ago

Why are you also mapping NULLs to 99991231? Is there something in the situation that demands this or was that just part of your own requirements?

William Salzman Over a year ago

@emtucifor It was a requirement from the business, they wanted to use 99991231 as an invaild/unknown date marker so that it would be easy to filter out these values from reporting.

ErikE Over a year ago

That makes sense. I prefer using 99991231 over NULL as well. It actually affects performance, too (GetDate() BETWEEN StartDate AND EndDate is so much better than any method of trying to deal with NULLs).

William Salzman Over a year ago

Thanks @Jondlm. I like the formatting changes you did on this answer!

HLGEM · Accepted Answer · 2010-02-11 14:27:41Z

2

I realize you have the fix you wanted, but the other method for dealing with issues of this sort is to use a conditional split. You can find and handle all sorts of data issues where you want to remove records from the main flow and do specific handling of them using conditional split. I find it especially handy when I want to be able to tell the vendor about the bad records they sent us so they can fix their data.

answered Feb 11, 2010 at 14:27

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

Dealing with Timestamp/Datetime when copying data from Oracle to SQL Server using SSIS

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related