Last week I was importing a bunch of CSV files to database using a SSIS package. What I found was that the CSV files were error free and as per my expectations except that few address information were inside double quotes and with commas. I did not know this as the CSV file size was huge and there were only few such instances. I discovered this only after I got some unexpected values in some DB columns.
So for example, I had a line in CSV file something like:
John Wood, “50, Canary Wharf London”, Cyclist
Though, MS Excel can read it correctly by default and ignores the comma within the double quote, SSIS by default would consider “50 and Canary Wharf London” as two different fields.
Once I discovered the problem, I had to go and change in my SSIS package and the solution was simple. You need to tell SSIS by mentioning text qualifier. Following was my change in the Flat file connection manager.