I feed data from Oracle into SQL Server. When I started with SQL Server, the person training me told me to convert dates to text in the Oracle View that feeds the data. Then in SQL Server convert it back to a date. If we didn't do this, we found that we would sometimes get errors on the date field. So this is how I've done things for a couple of years.

About two months ago I found the REAL problem. SQL Server does not support dates prior to January 1, 1753 but Oracle does. If the date is prior to that date, the source container in SSIS would fail. By passing as a text and then converting, the conversion fails but SSIS can be programmed at that step to ignore the failure.

So if I program the where clause to pass only date fields on/after January 1, 1753, then the problem goes away. I've started doing this on new feeds but many old feeds still exist.

Of course the real problem is in the application. An ORDER_CREATE date cannot have happened 250 years ago! The application should not be allowing these dates but...

Rob