I had a similar problem when taking information from Excel file via SSIS.
I ended up with using intermediate table (that is created natively with SSIS), and then I massage data from this table as needed.

Good Luck