There is a known issue where DTS truncates delimited columns at 255 characters when exporting to a text file. The problem is not with the DataPump itself, it is due to the default properties of text file connection provider as used within the DTS designer and the Import/Export wizard. The Wizard has been fixed (FIX: DTS May Truncate Characters When You Export a Table Column of Character Data Type to a Text File (247527)) in SQL Server 2000 Service Pack 2, but this is only for the wizard. If you manually build your DTS packages then this is still a problem.

The problem is really very simple, and as mentioned above, it is caused by the DTSFlatFile OLE-DB provider. On closer examination of the properties for this provider you may notice one called Max characters per delimited column. When manually building a package this property is set to 255, but if you use the "fixed" Wizard it is set to 8000. This leads to the obvious conclusion that by manually changing the value we can fix the problem for ourselves.

To fix a package, open the package in the designer, and then select Disconnected Edit from the Package menu within the designer window. In the Edit All Package Properties dialog expand the object tree for Connections. You can then identify your problem text file connection within those shown. Expand your connection and then expand OLE DB Properties. You should then see the property Max characters per delimited column. Select this item and change the Value property in the right hand pane.

There is no Disconnected Edit within the SQL Server 7.0 tools, but you can achieve the same effect through a simple piece of ActiveX Script code. To do this, add a new ActiveX Script Task to your package and paste in the code below. You may need to change the name of the Connection to match your package. Once the code is in place we need to execute it, but you probably don't want to execute any of your normal tasks. In SQL Server 2000 you have the Execute Step option within the designer, but again we have no such luxury in the SQL Server 7.0 tools. We can achieve a similar effect by temporarily adding some additional workflow constraints to prevent our normal tasks from executing. Do this in such a way as to form a loop of your existing tasks. The loop has no starting point, so when we execute the package the only task available to execute is our temporary ActiveX Script Task. In addition remember that multiple constraints leading to a single task must both be satisfied for that task to execute, so you can also use multiple constraints and also constraints of a conflicting precedence basis to prevent a task from executing. Once execution has completed you can then clean up the package by deleting the temporary task and workflow you have just added. The property has now been set, so save your package to persist this change.

' Pkg 297 (DataPump 255)
Option Explicit

Function Main()

	Dim oConn
	Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
	oConn.ConnectionProperties("Max characters per delimited column").Value = 8000
	Set oConn = Nothing

	Main = DTSTaskExecResult_Success
End Function