I have a SQL Server 2000 DTS Package, where within a Data Transformation
task, with source being a text file delimited with ~¬ and destination being a
SQL Server table, I tried to use the "Trim String" transformation to transfer
column by column (one to one mapping). Each Transformation has "Trim leading
white space" and Trim trailing white space" ticked. However, there are
trailing white spaces in the destination SQL Server column (like how the data
appear in the source file. How could I solve this??

Alternatively, are there any simple SQL I can run to clear trailing
whitespaces on all columns on all rows in a given table?

You are saying that the trim ios not working? Are you using fixed width
columns in SQL? If so have a look at SET ANSI_PADDING in Books Online.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Patrick" <questions (AT) newsgroup (DOT) nospam> wrote

Are you using an Excel file as the source for your DTS import? Is it
importing into an nvarchar field in your database? The only time I have had
this happen is in that scenario I described. I did not bang my head against
it to much, but I suspect that the "leading or trainling" spaces you are
describing are unicode characters. I know that in the Excel document I am
refering to the fields started with Excel's text designator ( ' ). Anyway,
there's a couple things you may want to check.

When I have had this problem with insert on DTS, no matter what trimming I
did, the DATALENGTH() would remain 5 (even if it was only 2 characters of
data). Fortunately for me it just goes into a staging table and it gets
trimmed and formatted before it gets put into the final table :-P

Best of luck,

John Scragg



"Patrick" wrote: