Importing CSV flat files in SSIS – dealing with Double Quotes

I’ve gone through a number of phases in SSIS. The first was fascination, the next was anger, then came love, and now I’m somewhere in the middle. One major flaw I’ve noticed is how SSIS handles double quotes (“) in imported flat file data, or should I say how it doesn’t handle it at all?

Simple scenario, I have a *csv file that I’m importing to a db table. The flat file is all text delimited using double quotes. A number of the fields contain literal double quotes, which in any CSV text delmited implementation you would normally escape by using another double quote delimeter. To illustrate:

This:

he cried out “SSIS!” in frustration

Becomes This:

“he cried out “”SSIS!”" in frustration”

Simple, standard csv behaviour, parses fine in excel.

Try importing to SSIS and your import will crash out, and if you try to preview that row you’ll get: “The flat file parser does not support embedding text qualifiers in data”

Solution? The only way I’ve found to get around this is to write your own escape functionality. I piggy-backed off html encoding and replaced literal quotes with "  , and then used derived columns with REPLACE(ImportedField,”"”,”"”) to change them back prior to putting them in their destination table.

I hate having to write work arounds like this, especially for functionality which should be part of the underlying core system. If anyone has a better way, please drop your comments in below.