I had a similar issue in SSIS 2008 with an Excel file that had columns wider than 255 characters, as well as embedded text qualifiers and delimiters. A real pain as Microsoft were not handling embedded delimiters well in SSIS at the time. I got around it by saving from Excel in csv format, then putting a Schema.ini file in the same folder as the csv, and specifying the data types of each column (Memo for long strings, or Text, Date, Double etc) within the Schema.ini file. In Connection Managers I chose "New OLE DB Connection", New Provider: Microsoft Jet 4.0 OLE DB Provider.

Clicked "All" in left pane and set Extended Properties to the following (without the quotes): "text;HDR=Yes;FMT=Delimited"

Clicked "Connection" in the left pane and set Database file name to the folder where the csv is (without including the csv filename).

Created an OLE DB Source in the Data Flow and pick the new connection manager in the first dropdown.

Set data access mode to "SQL command" and set the SQL command text to "SELECT * FROM myexportname.csv" or whatever the name of the csv file is.

The text within the Schema.ini file should look something like this:

[myexportname.csv]

ColNameHeader = True

Format = CSVDelimited

DateTimeFormat=dd/MM/yyyy

Col1=Category Text

Col2=Registration Text

Col3=Description Text

Col4="Sales Big Description" Memo

etc....

If you have to load multiple csvs from a single folder with this method, they all must use the same Schema.ini file but this file can include separate schemas for each csv filename.

Not fun to set up but once done it is reliable.