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

Sumit Mund is an Artificial Intelligence Consultant with more than 12 years of experience. He has an MSc by Research degree and B.Tech degree in Information Technology. He is also a part-time PhD scholar at University of Huddersfield where his research area includes applications of Deep Reinforcement Learning and uses Google Tensorflow extensively. Read More...

This Post Has One Comment

  1. Thanks dude, I was losing my mid with this.

Leave a Reply

Close Menu