I just (yet again) ran into this Excel 'feature' and it managed to irritate me just as much as it always has.
This time, however, I tried a different solution: I added calculated columns to the right of the main data that forced the datatypes of the data to the left.
So - if column A contains a 1, but might also contain non-numeric data, add a calculated column to force it to be read as text and use that in your import (=Text(An,0)).
Note - this worked within a VBScript import of Excel data. Not tried it in SSIS, but should work ...
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.