CODEWITHSUNDEEP
ssis
skyline01
skyline01

Reputation: 2101

SQL Server: LEN gives unexpected results

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

Upvotes: 0

Views: 86

Answers (1)

skyline01
skyline01

Reputation: 2101

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.

Upvotes: 1

Related Questions