Problem: When trying to load a large text string > 250 characters into a varchar(5000) field in my SQL Server 2000 destination database (using either an Active X transformation or copy column transformation) from an Excel or flat-file data source my data is being truncated. Only the first 248 characters are being loaded into the varchar(5000) field

Has anyone experienced this issue before and if so can someone please provide me with a suggestion to fix this problem

Best Regards
Whalen

How do you know they are being truncated?

QA has a default limit of chars you can see

Tools | Options | Results - Max Characters per column

Here was my test

Create an Excel spreadsheet
Fill A2 will 471 characters, A3 = len(A2)

Create a SQL Server table

Create table HowBigFromExcel(col1 varchar(5000), col2 int)
GO

Pump the data into the table

Now do

SELECT col1, DATALENGTH(col1),col2 FROMHowBigFromExcel


the second and third columns in the output both read 471




--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Whalen" <whalenrun (AT) hotmail (DOT) com> wrote

an Active X transformation or copy column transformation) from an Excel or
flat-file data source my data is being truncated. Only the first 248
characters are being loaded into the varchar(5000) field.

Allan,

I didn't know the data was being truncated, I just assumed it was - wrongfully so. Thanks for the info on SQL QA.

Regards,
Whalen