Sql Server DTS Truncates Text While Exporting: Fix

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.

WORKAROUND

loadTOCNode(1, ‘workaround’); 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.

One Response

  1. Good suggestion, but do you have any advice on if one needs to use the default (non-fixed-length) option.Currently, I am converting the varchar to a text field, exporting, and then converting it back. Bleh!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: