Can you bring the data into a staging table and use SQL to convert and load the final table? I find that to be a quick way to get it done. Truncate the staging table, load it as text or however it comes to you, then create an execute SQL task that loads the final table with the data from the staging table. If the data is clean an implicit conversion may happen and work just fine; if not, then manipulate the data in the SQL - using an explicit CONVERT or CAST.
Hope that helps.