Hi,

I try to import a text file (CSV formatted) into a table in SQL Server 2000.
For doing so, I am using a DTS package.
The problem is that all text fields are truncated after 255 characters.

I spended a good part of last night trying to find a solution,
unsuccessfully!

I am using SQL Server 2000 SP3. Also I have read the article: "DataPump
truncates delimited fields to 255 characters" @ http://www.sqldts.com/?297
but it seems not to be my probelem as the field 'Max characters per
delimited column' already had a value of 8000 by default. I guess this is
because I have SP 3 and I created the DTS through the Import Data wizard as
explained in the article...

Then I am stuck and I cannot find any other properties to change to help.

In a maybe not related note, I had the same problem when reading data from
an Excel sheet into my .Net code and I had to change a key in the Windows
registry to solve the problem. The key was a configuration key for the ODBC
driver of Microsoft Excel (JET engine). See KB Data truncated to 255
characters with Excel ODBC driver
http://support.microsoft.com/default...;EN-US;Q189897

As I am not guru in DTS I do not know what kind of driver the DTS engine is
using. But if it is using some kind of driver and if that driver has some
kind of similar settings for the data length, it may be the source of my
problem.

Also I am even not sure what the DATA PUMP word refers to. All I see in my
very simple DTS is 2 connectors (one to my source text file and one to my
SQL DB) and a "transformation" object that will get rows from the source and
insert them in the target.

I hope that someone can help me out here I am kinda desesperate to make this
work.

Best,

Francois

**Update**

I just checked the value I have from the text file by having a MsgBox pop up
in the ActiveX script of the DTS transformation. The message box shows the
full value of the text field! This seems to mean that the all value (about
800 characters) is read properly from the CSV file.
It may mean that it's at insert time that the string get truncated but it
cannot be because of the DataType of the Column in the SQL table as it's a
nvarchar (1000). Far more than 256 characters....
Anybody has a clue with this additional information?

Best,

Francois Malgreve

"Francois Malgreve" <francois.malgreveADgmail.com> wrote