Strings Truncating on Export to Flat File

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  February 13, 2004 at 6:36 am

  #63380

  I have a table with a single column defined as varchar(1000). I need to export that table to flat file. I set up a DTS package with a SQL Source and a Text File destination. I used Copy Column to copy the source column to the destination file. All the records were truncated at 256 chars.

  I changed the Transformation from Copy Column to an ActiveX script and tried again. Same result.

  Just to confirm I ran "Select len(userdata) as length from users" and all the values in the column are longer than 256 (i.e. 589).

  Is this a limitation of DTS? Is there a way around it? If not, is there another method (i.e. a Stored Procedure) I can use to create this flat file? (btw we aren't allowed to use xp_cmdshell just in case you were going to suggest a script with that in it).

  

   
  
  

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  February 13, 2004 at 7:16 am

  #493976

  

  I answered my own question. I found the following while searching on Google:

  http://support.microsoft.com/?kbid=247527

  I used the following from that article and it fixed the problem:

• In SQL Server 2000, an additional workaround exists if the delimited text files must be used. When you use the DTS Import/Export Wizard to export data to a delimited text file, first save the package, and then open the package in the DTS Designer. In the DTS Designer, click Package, and then click Disconnected Edit to open the package in Disconnected Edit mode. Expand Connections, expand the text file connection object, and then expand OLEDB Properties. Finally, change the value of "Max characters per delimited column" to 8,000.