none
SSIS Issue loading the data from SQL table into .csv file - Comma is within the data RRS feed

  • Question

  • Hi,

    My source is a SQL table and i'm fetching some data in which there are few fields which has comma within the data. I'm trying to load this data into csv file (flat file destination).

    While loading the data, say for example, i have a field with data "Boston,MA" the csv file outputs it as:

    "Boston in one field

    and MA" in another field.

    My field is not fixed length.

    I don't want any special character to be replaced for comma and i need the data as it is in from the SQL table. I tried enclosing the column values within double quotes but even that didn't help out.

    Is there any way of handling this issue in SSIS ? Is this a known issue that SSIS can't handle while loading into CSV ?Please help me understand this load process

    Tuesday, April 1, 2014 3:39 PM

Answers

  • In the Flat File connection manger editor,

    1. Choose the format as Delimited.
    2. Text Qualifier as "
    3. Row Header delimiter as comma.


    Regards, RSingh

    Tuesday, April 1, 2014 4:04 PM

All replies

  • In the Flat File connection manger editor,

    1. Choose the format as Delimited.
    2. Text Qualifier as "
    3. Row Header delimiter as comma.


    Regards, RSingh

    Tuesday, April 1, 2014 4:04 PM
  • Thank You RSingh!

    So do you want me to put every column in source data within double quotes ("") ? I do have other columns without comma within the data.

    Tuesday, April 1, 2014 4:29 PM
  • I did not say to put "" in every column. Open the "Flat File connection manger" and set the properties as posted above.


    Regards, RSingh

    Tuesday, April 1, 2014 5:16 PM
  • Thanks RSingh!! It Worked !!!
    Tuesday, April 1, 2014 6:11 PM
  • Putting double quotes as the text qualifier for every column may not be necessary, however you may want to go ahead and add it if other columns you're importing could potentially have commas in the future.

    After you've done a lot of name/address or location imports, you get used to names, trusts, county roads, house/apartment numbers, etc getting commas every once in a while.

    Tuesday, April 1, 2014 11:23 PM