SSIS File Destination to CSV

Question

SSIS File Destination to CSV

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
October 23, 2013 at 9:37 am

#281693

I can't be the first person in the history of SSIS/SQL to have this problem, but yet I can't find anything of value on the Google...
I want to take data from a SELECT statement and output the results to CSV. That's easy, I do this 100k times a week. This time, the end users (let's call them Dumb-Dumbs) decided to add comma's to the data in the sql table. The only way I can get the output to stop comma separating EVERY comma in the field was to use QUOTENAME(field,'"') and use a text qualifier of "" (yes, two of them) on the flat file destination. It's okay, but now the results in the CSV show up with double quotes around them and my date fields are a disaster.
How can I get data from the SQL statement to display properly in the CSV file? Is there a way to "wrap" the output from the select statement or output a delimiter other than comma? Or is there a way to get SSIS package to handle this gracefully?
Please do not tell me to add a function to strip punctuation. Let's just assume that any creating of functions or tables is not an option at this point.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pshields SSC Veteran Points: 213 More actions · Answer 1

You can handle commas in the data if you use a double quote as a text qualifier. In the flat file connection on the General page set Format to Delimited and Text qualifier to ". Now all of your data fields should be wrapped by double quotes. The destination for my file required the double quotes around each field so I had to deal with wrapping quotes within the data with a single quote.

Phil

Phil Parkin SSC Guru Points: 247037 More actions · Answer 2

Even better (IMO) is to use something rare as your column delimiter (eg |).

Then you don't need the extra "s & save some disk space...

dg227 SSCrazy Points: 2017 More actions · Answer 3

Phil Parkin (10/24/2013)
Even better (IMO) is to use something rare as your column delimiter (eg |).

+1

Any time you can get away from the csv (quoted, or not) format - and assuming your business rules/requirements allow this - the better off you'll be. This makes it easier and cleaner when dealing with Excel views of the file (just change your delimiter) and possible re-import into systems via other processes.

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 4

I did this 50 flippin times yesterday and it didnt work. Today, worked like a charm. I guess SSIS likes Thursday's better than Wednesday's.... :sick:

Thank you everyone!