Importing CSV file with double quotes using SSIS

Tweet about this on TwitterShare on LinkedInShare on Google+Share on Facebook

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

About Sumit Mund

Sumit Mund is a big data analytics consultant with about a decade of industry experience. At Mund Consulting Ltd, he is a director and acts as the lead consultant. He is an expert in machine learning, predictive analytics, Apache Spark, Python, C#, R, and Scala; Sumit has an active interest in Artificial Intelligence. He has extensive experience working with most of Microsoft Data Analytics tools and Big Data platforms like HDInsight. He is a Certified Developer on Apache Spark and also Microsoft Certified Solution Expert (MCSE in Business Intelligence). Sumit regularly engages on social media platforms through his tweets, blogs, and LinkedIn profile, and often gives talks at industry conferences and local user group meetings.

One thought on “Importing CSV file with double quotes using SSIS

Leave a Reply

Your email address will not be published. Required fields are marked *

five + 9 =