FIX: DTS May Truncate Characters When You Export a Table Column of Character Data Type to a Text File

When you use the Data Transformation Services (DTS) Export/Import wizard, DTS may truncate column strings that are over 255 characters long if all of the following conditions exist:

• The column is a character data type (varchar, char, nvarchar, nchar) and the column length is greater than 255 characters.
  
  
• DTS exports the column to a text file.
  
  
• Delimited fields are used for exporting.

When you use delimited fields to export data, the OLE provider for text files is used by DTS, DTSFlatFile (dtsffile.dll), which causes the data to truncate on export.

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

Here are several methods that you can use to work around this problem:

• Use fixed fields and export to a text file with the DTS Wizard or Package Designer. Both the DTS Export/Import Wizard and the DTS Package Designer provide the option to use fixed fields when you export data to a text file.
  
  To use the DTS Export/Import wizard, you will see the following prompt when you choose the destination text file:
  Select Destination File Format
  You want to select Fixed field - information is assigned into columns of equal width.
  
  To use the DTS Package Designer, you will see the following prompt when you are setting up the destination text file:
  Select file format
  You want to select Fixed field - information is assigned into columns of equal width.
  
  NOTE: Because the use of fixed fields might require more space than delimited fields if your varchar columns are not filled up, this operation may potentially use more space. The fixed field requires more space because space is allocated to the whole width of the column. However, the next option eliminates this problem.
  
  
• Use bcp to export the table to a text file. Bcp provides features that allow you to export tables or views to text files by using delimited fields. For more information about the bcp utility, see Microsoft SQL Server 7.0 Books Online.
  
  
• Create the table column by using a text data type.
• 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.
• If you create the package by using the DTS object model, add the following line of code where the text file connection is being initialized:

  oConnection.ConnectionProperties("Max characters per delimited
  				column") = 8000

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.