SSIS Export of nText & nvarchar to csv flat file

• bkirk

  SSCrazy

  Points: 2824

  More actions

  January 29, 2009 at 3:37 pm

  #383835

  I'm trying to export SQL Products table containing both ntext & nvarchar fields out to a flat file destination using SSIS package.

  All data fields for the products table are entered through a web form. However, when exporting to csv 2 fields are troublesome:

  1) ProdShortDesc(nvarchar(255)) - entered using a multiline text box which sometime contains return lines hence the trouble.

  2) ProdLongDesc(ntext) - entered using a WYSIWYG editor to insert HTML hence the need to be ntext type.

  My basic SSIS package design is:

  1) OLEDB Source connection (select statement on table)

  2) Data Conversion OR Export Column

  3) Flat File Destination

  Please note that i import the flat file into Excel to check the column alignments are correct afterwards.

  Here are my trial runs:

  1) all standard table columns work correctly if i exclude the short and long descriptions

  2) Including ProdShortDesc column does not align the columns correctly due to the return line within the data, hence something to do with formatting of UTF-8 versus Unicode? Need help here.

  3) Including ProdLongDesc column causes failures in the package obviously due to the ntext type. How can i export an ntext column?

  I've tried using separators like (:,;|) a few times as well as text identifiers with ".

  Any help is appreciated or a good link to a tute or video?

  I might have to send this one to Brian Knight at JumpStartTV.

  Thanks in advance

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  January 30, 2009 at 10:47 am

  #935725

  bkirk (1/29/2009)

  ---

  I'm trying to export SQL Products table containing both ntext & nvarchar fields out to a flat file destination using SSIS package.

  All data fields for the products table are entered through a web form. However, when exporting to csv 2 fields are troublesome:

  1) ProdShortDesc(nvarchar(255)) - entered using a multiline text box which sometime contains return lines hence the trouble.

  2) ProdLongDesc(ntext) - entered using a WYSIWYG editor to insert HTML hence the need to be ntext type.

  My basic SSIS package design is:

  1) OLEDB Source connection (select statement on table)

  2) Data Conversion OR Export Column

  3) Flat File Destination

  Please note that i import the flat file into Excel to check the column alignments are correct afterwards.

  Here are my trial runs:

  1) all standard table columns work correctly if i exclude the short and long descriptions

  2) Including ProdShortDesc column does not align the columns correctly due to the return line within the data, hence something to do with formatting of UTF-8 versus Unicode? Need help here.

  3) Including ProdLongDesc column causes failures in the package obviously due to the ntext type. How can i export an ntext column?

  I've tried using separators like (:,;|) a few times as well as text identifiers with ".

  Any help is appreciated or a good link to a tute or video?

  I might have to send this one to Brian Knight at JumpStartTV.

  Thanks in advance

  For the new lines, you have to write line feed (0A) to the output. I have just tested an export of CSV from Excel and the new lines were exported with line feed character.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  

• farside41

  SSCrazy

  Points: 2083

  More actions

  June 1, 2012 at 8:18 am

  #1495887

  CozyRoc (1/30/2009)

  ---

  For the new lines, you have to write line feed (0A) to the output. I have just tested an export of CSV from Excel and the new lines were exported with line feed character.

  Can you clarify what you mean here? How do you do this?