Okay, I know this is an old post, but I recently had the same issue and this was one of the first threads I found that described the issue. Now that my import is running, here is what I did.
The original issue on this thread being that SSIS has a hard time importing data that is sometimes text qualified. This seems to be a pretty common way for Excel or MS Access to export data.
In my case, I had a comma-delimited file with a field of numbers. Numbers less than 1000 were not text qualified. Numbers >= 1000 were written containing commas and text qualified with double quotes.
So a sample set of records looked like this:
Color,Date,Purchases
Red,"Sep 1, 2014",500
Blue,"Sep 2, 2014",750
Green,"Sep 3 2014","1,000"
If you tell SSIS that the Purchases field is text qualified, it looks for the qualifier in the first two rows and fails to parse the data correctly. If you tell it the data is not text qualified, it finds the "extra" comma in the third row, and fails to parse correctly.
The solution that got me most of the way there was posted here:
http://geekswithblogs.net/sathya/articles/how-to-import-and-export-csv-files-directly-in-ssis.aspx
Steps:
Create a new connection Manager
Select New Oledb Connection
Select the Provider as Microsoft Jet 4.0 Oledb Provider
After choosing that on the same window at the left you will be having two tabs to switch over. one would be All (Which mentions the properties of the connection) and other would be the connection info
Choose All. You would see a property called ExtendedProperties.
In that paste this without quotes : "text;HDR=Yes;FMT=Delimited" --I actually used CSVDelimited
There will be another property called Mode, which will mention 16 (readonly) and if you want to make that Read/Write change it to 19.
In the Server or file name text box under the connection tab, give the path of the csv file. Give it only till the folder name and not the csv file itself. (Ex: If your file is c:\sathya\contact.csv give it as c:\sathya)
And you can use this Oledb Connection in Oledb Source Task, Lookup task and Oledb Destination task also if you have changed the mode to 19 i.e. read/write.
While writing the query to fetch from csv, since you have given only up to the folder as the datasource you need to write query like "select * from contact.csv"
At this point, my import ran. However, my Purchases data still came in as NULL values where the source contained commas. Progress.
Then, based on this:
http://kb.tableau.com/articles/knowledgebase/jet-incorrect-data-type-issues
I went into the registry under HKLM\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text
and changed the string value for ImportMixedTypes to Text.
For good measure, I added the rows key set to 0 (scan all rows for type) as well.
Data now imports as a string (with the commas).
In my case, I used the DerivedColumn task to strip out commas on the way in so I can treat it as a number too.
Anyway, my 2 cents. Good luck.