none
Need to export text file with embedded delimiters

    Question

  • I'm in the process of converting a number of DTS package exports to SSIS.  The final text files will be pipe-delimited.  While most of the data export has gone smoothly, some of the text fields I'm exporting include quotes as part of descriptions to represent "inches" (i.e., 9" Pie, etc.).  Under the existing DTS packages, when these fields are exported, the quotes are doubled to indicate that the " symbol should be read as embedded, and is not the end of the delimited text.  When I export the same data via SSIS, the " symbol isn't doubled, and therefore won't import correctly into the legacy application at the other end of the process.  I've looked at the various "UnDouble" sample SSIS components that have been posted, but they seem to be intended for going the other way, from a text file into SQL, not from SQL to a text file.  I've also tried my hand at doubling the quotes via a Derived Column transformation, but haven't been able to get it to double the quotes.

    Any suggestions will be welcome...

    Thanks in advance,

    Joshua Moldover
    DBA, UNFI
    www.unfi.com
    Monday, February 02, 2009 4:34 PM

Answers

  • I was able to solve this problem.  For anyone else who has a similar issue, it's a simple solution.  Using the DerivedColumn transformation, replace the data in the field with the following command:

    REPLACE([FIELDNAME],"\"","\"\"")

    • Marked as answer by JMoldover Monday, February 02, 2009 5:31 PM
    Monday, February 02, 2009 5:31 PM

All replies

  • JMoldover said:

    I'm in the process of converting a number of DTS package exports to SSIS.  The final text files will be pipe-delimited.  While most of the data export has gone smoothly, some of the text fields I'm exporting include quotes as part of descriptions to represent "inches" (i.e., 9" Pie, etc.).  Under the existing DTS packages, when these fields are exported, the quotes are doubled to indicate that the " symbol should be read as embedded, and is not the end of the delimited text.  When I export the same data via SSIS, the " symbol isn't doubled, and therefore won't import correctly into the legacy application at the other end of the process.  I've looked at the various "UnDouble" sample SSIS components that have been posted, but they seem to be intended for going the other way, from a text file into SQL, not from SQL to a text file.  I've also tried my hand at doubling the quotes via a Derived Column transformation, but haven't been able to get it to double the quotes.

    Any suggestions will be welcome...

    Thanks in advance,

    Joshua Moldover
    DBA, UNFI
    www.unfi.com

    I forgot to mention - the system I'm using is SQL 2005 SP2 Enterprise 64-bit.

    Monday, February 02, 2009 4:45 PM
  • I was able to solve this problem.  For anyone else who has a similar issue, it's a simple solution.  Using the DerivedColumn transformation, replace the data in the field with the following command:

    REPLACE([FIELDNAME],"\"","\"\"")

    • Marked as answer by JMoldover Monday, February 02, 2009 5:31 PM
    Monday, February 02, 2009 5:31 PM