locked
Export to CSV format - Comprison of DTS and SSIS process ... RRS feed

  • Question

  • Noticing a weird issue. I have rewritten a DTS package that exports data from a set of SQL Server tables into a CSV file. When I run the DTS package, it exports into a column the following value:

    'DYNA-MIX/GRAFTON, WV 90664002

    The same value is exported as

    DYNA-MIX/GRAFTON

    AND

     WV 90664002

     

    The problem is that in SSIS the value gets split into two values because of an embedded comma value in the data element and puts them as two seperate columns. Was the functionality changed in SSIS? If not, what am I missing?

     

    TIA

     


    Jagannathan Santhanam
    Friday, August 5, 2011 2:46 AM

Answers

All replies

  • In the columns tab of your Flat File Conenction manager, change "Column delimiter" from "Comma {,}" to "{CR}{LF}". It must do the job.
    http://thebipalace.wordpress.com

    Friday, August 5, 2011 3:45 AM
  • Check this
    Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
    Friday, August 5, 2011 5:07 AM
  • Neither suggestions work. SaeedB's suggestion ends up with an error "The row delimiter cannot be the same as the column delimiter".

     

    Deepak_MSBI's solution does not work either.


    Jagannathan Santhanam
    Friday, August 5, 2011 12:51 PM
  • This is the solution that ended up working for me. Make sure you set the Text qualifier to " (double quotes) in the Flat File COnnection Manager Editor under the General tab. This setting worked for me.

    Also, here is the response from Microsoft based on my request to them:

    https://connect.microsoft.com/SQLServer/feedback/details/683167/export-to-csv-format-using-ssis


    Jagannathan Santhanam
    • Marked as answer by jsanthanam Friday, August 5, 2011 5:39 PM
    Friday, August 5, 2011 5:39 PM