Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DTS export to text file with > 256 chars/column

Status
Not open for further replies.
foxdev

foxdev

Programmer
Feb 11, 2000
1,995
US
I've got a table with a column as varchar(3000). In trying to use DTS to write this out to a text file, this particular column is cut off at about 255 characters (give or take one).

Is this a limitation of DTS, or can something be done to get around it? The BOL seems silent on any limitation, and I can use a client tool and 30 lines of code to do it, but we're trying to use DTS for as much as possible due to some logistical issues.


Robert Bradley

 
Sort by date Sort by votes
Hi Robert,

I am unaware of a limitation with varchar and DTS.

It is a txt file that you are writing to?

While I'm digging for an answer... you could stay in DTS, and write a vbscript transformation that uses FSO to output the lines of text... this would work and will keep you in DTS...

Tom Davis
tdavis@sark.com
 
Upvote 0 Downvote
The size is defined in the output text file as 3000, quoted.

To add to the bizareness (bizarity?), when we try to import that text file into Excel, some of the rows have the column cut off at byte 255, whereas most do not. I know you're wondering: why the heck are you exporting out to a text file then into Excel? Well, because exporting directly to Excel always cuts off as well; at least with the text file, we eliminate any inherent Excel limitations.

Robert Bradley

 
Upvote 0 Downvote
Based on what you've defined as your row delimiters, do you have those characters stored in your varchar(3000) source column?

And...are you using fixed-field or delimited destination columns?

Tom Davis
tdavis@sark.com
 
Upvote 0 Downvote
Here are my settings, FWIW:
dts_advanced.gif


I'll check for the pipe character in the column, but I'm 98% sure its not there. I'll report back if I find any in there...good point.

Robert Bradley

 
Upvote 0 Downvote
FYI

I asked an MCS SQL Server guy about this last night about this and he said that he doesn't know of a problem with this... (the truncation of chars)...

Tom Davis
tdavis@sark.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
977
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
954
BobSuruncle
BobSuruncle
ahmedsa2018
  • Locked
  • Question
How to export 20 milion rows to only one file ?
Replies
2
Views
351
JeffModen
JeffModen
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top