I have the following code. The problem is that the source cell (BTN) can be either empty (NULL), be a number formatted as text, or be stored as a number. When I run the transformation, it sees any row that has a number, stored as a number, as NULL and will not import it. I have tried using CStr() around the source value, but that does not seem to help. Any ideas?
If IsNull(DTSSource("BTN")) Then
Main = DTSTransformStat_SkipRow
ElseIf IsNumeric(DTSSource("BTN")) Then
DTSDestination("BTN") = CDbl(DTSSource("BTN"))
DTSDestination("RequestID") = DTSSource("RqstID")
Main = DTSTransformStat_OK