Have you tried using the isdate function, i.e.:-

If IsDate(DTSSource("Date")) =1 Then

    DTSDestination("Date") = DTSSource("Date")

Else

    DTSDestination("Date") = Null

End If

Main = DTSTransformStat_OK