Something weird that occured today: I had an Excel file that I needed to import into my SQL Server 2005 database. I followed the instructions but kept getting an error.
Error 0xc020901c: Data Flow Task: There was an error with output column "Agenda 2" (63) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
The Jet driver by default looks only at the first 8 columns to guess the maximum size of the rows. So if your first 8 columns are smaller than 255 characters and column9 has more characters it will crash.
Make a change to your machine's registry to increase the number of columns to check for:
The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large.