Many times when importing data between Oracle and MS SQL you will encounter problems with dates. MS SQL 2005 valid date range differs from Oracle in that it must be between 1753-01-01 and 9999-12-31.

You can validate the date in SSIS by using a transformation script component. The following script snippet can be used to make sure the date is in the range allowed by the target MS SQL database.

Try
Dim dt As DateTime = System.DateTime.Parse(Row.c2)
Dim dtMin As DateTime = System.DateTime.Parse("1753-01-01 00:00:00")
Dim dtMax As DateTime = System.DateTime.Parse("9999-12-31 23:59:59")
If (dt < dtMin Or dt > dtMax) Then
Row.c2_IsNull = True
EndIf
Catch ex As Exception
Row.c2_IsNull = True
EndTry

The Row.c2 is the name of my column and I am setting it to Null if it is outside of the range or is invalid. You could set this to any other valid date that your target is expecting.