Flat File to retain commas from SQL data

问题:

I’m importing a SQL view to SSIS using the Flat File Connection Manager. One of my columns in SQL has comma(s) in it. (123 Main St, Boston, MA) . When I import the data to SSIS, the commas within the column are being treated as delimiters, and my column is being broken into several columns. I have done a lot of research online, and have followed some workarounds which aren't working for me.

In SQL Server, I added double quotes around the values that have comma(s) in it.

' "'+CAST(a.Address as varchar(100))+'" ' 

So, 123 Main St, Boston, MA now reads “123 Main St, Boston, MA”

Then in my SSIS Flat File Connection Manager,

In the General tab:

• Text Qualifier is set to “

• Header Row Delimiter is set to {CR}-{LF}

In the columns tab:

• Row delimiter is set to {LF}

• Column delimiter is set to Comma {,}

And in the advanced Tab, all of my columns have the Text Qualified set to True.

After all of this, my column with commas in it, is still being separated into multiple columns. Am I missing a step? How can I get the SSIS package to treat my address column as one column and not break it out to several columns?

EDIT: Just to add more specifics. I am pulling from a SQL view that has double quotes around any field that has commas in it. I am then emailing that file and opening it in MS Excel. When I open it the file it read as follows:

123 Main St Boston MA" " (In three cells)

And I need it to read as

123 Main St, Boston, MA (in one cell)

回答1:

Have a look of this - Commas within CSV Data

If there is a comma in a column then that column should be surrounded by a single quote or double quote. Then if inside that column there is a single or double quote it should have an escape charter before it, usually a \

Example format of CSV

ID - address - name 
1, "Some Address, Some Street, 10452", 'David  O\'Brian'

• Change every comma values with another unique delimiter which values haven't any of the characters inside,like : vertical bar ( | )
• Change column delimiter to this new delimiter , and set text qualifier with double quote ( " )
• You can automate the replace process using a Script Task before Dataflow Task for replacing delimiters. You can use replace script form here.

Also have a look of these resources.

• Fixing comma problem in CSV file in SSIS
• How to handle extra comma inside double quotes while processing a CSV file in SSIS

回答2:

I ended up recreating the package, using the same parameters that are listed in my question. I also replaced this

' "'+CAST(a.Address as varchar(100))+'" ' 

with this in my SQL view

  a.Address 

And it now runs as desired. Not sure what was going on there. Thanks to everyone for their comments and suggestions.