If you want to read an Excel file, you probaby have already an Excel File Connection Manager set-up.

Do the following:

Click on the connection manager. Then, go to the properties window (usually at the right in BIDS).

There you will find the propery ConnectionString. Edit this connection string manually by placing IMEX=1 at the end. The connection string should end like this:

;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";

Another thing to do is changing a registry setting (on all the computers that will use the package). This registry setting determines how many rows Excel will scan to determine the datatype of a column. Standard this is 8, meaning that if your first 8 rows are numerical and all the other rows are strings, IMEX will do nothing. See here:

http://support.microsoft.com/kb/189897