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?

Answer 1

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.

Answer 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.