The connection manager for excel uses the Excel Jet Driver. What this does when you create your source and destinations is sample the first 8 records and based on what it finds in the column sets your external columns accordingly.

Even if you have set the column lengths in the Excel file using SQL, you may still find that SSIS fails where the data in your spreadsheet isn't of a length that you've specified (i.e. If you've set a column to be DT_NTEXT yet in your spreadsheets first 8 rows there are no column lengths longer than 255 characters).

There are options to deal with this

1. If you are going to be writing to a new Excel file each time, create a template which has a dummy row as the first record which contains enough characters for your datatype.; copy it to a new file; and make this your destination each time.
2. If you are appending to an existing file either a) Alter the Registry setting so that it samples more records. or b) put one record that does meet the specified length criteria (>255 characters) as the first record.
3. A note about changing the registry - this must be done on all machines that are going to run the package as otherwise it will work fine on your machine, but at runtime a server running it will fail.

Issue & registry config instructions in slightly more detail