SQL Server: LEN gives unexpected results

0
votes

I am working in SQL Server 2008 and SSIS (BIDS). I have a data flow task that takes a flat file into a destination table. All columns in the destination table are varchar(255).

I am querying the destination table, and I'm noticing some odd behavior.

For instance, in a certain column, several values are '0.00'. When I run LEN against this column, I get 7 for this value, not 4. Why is this happening? Is SSIS known to introduce non-ASCII characters in the data flow task?

An example of the flat file is:

A|stuff||x||061|-117.96|0.00

An example of my table schema: sp_help

1
ssis
Show the flat file sample and the table schema – dario
No, SSIS is not going to introduce any value that does not already exist in your data. So, when you're running your query that determines the length of 0.00 is 7, what else is there? I find a query like SELECT 'x' + T.Seq1 + 'x' FROM T can help illuminate where space is. My assumption is that you'll have trailing space from your source. Apply an RTRIM operation on the data before you store it. – billinkc
You may also have non-trimmable whitespace characters in your data. Use an ASCII decoder to check for those: asciivalue.com. Someone here loves nonbreaking-spaces... – Eric Hauenstein
@billinkc: I tried the 'x' + T.Seq1 +'x' method. I'm not seeing any leading or trailing white spaces. – skyline01

1 Answers

1
votes

I figured out what I was doing wrong. When I first created my SSIS package, the text files I was operating on had {LF} as their row delimiters. But, the current text files that I'm operating on have {CR}{LF} as their row delimiters. So, upon import, SQL maintained the {CR} character in the last column of each table. Changing the SSIS package row delimiter to {CR}{LF} on all connection managers resolved the problem.