Why not split the two types out inside SSIS:

select someData, ISNUMERIC(someData) As NumTest

from test

WHERE ISNUMERIC(someData)=1;

The example is kind of overdone, but the idea is that you can test/filter for whether the column contains numeric data, and route that data inside your dataflow... and just send the non-numeric data into a different one.