Importing CSV file with double quotes using SSIS

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.

Flat File Connection Manager

Sumit Mund

Data Solution Architect with more than 15 years of hands-on experience. He has an MSc by Research (in AI) degree and B.Tech degree in Information Technology. He is also a part-time PhD scholar at the University of Huddersfield where his research area includes applications of AI in Finance, particularly in Risk Management (Hedging). Read More...

Leave a Reply

Close Menu