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.
SQL Server 2000 Workaround
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.
SQL Server 7.0 Workaround
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)
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.ConnectionProperties("Max characters per delimited column").Value = 8000
Set oConn = Nothing
Main = DTSTaskExecResult_Success