Using the Lookup Transform on a varchar field

[FlaBusInt]

I pull data from a SQL table that has a column that can be 7 - 9 characters long which has a varchar(9) data type. I pass that column into a Lookup transform where the lookup is done against another table with a varchar(9).

My problem is that the Lookup seems to be throwing an error on any data that is not 9 characters long.

Do I have some kind of padding issue, and if so, how do I fix it? I've redirected the error rows to a viewer and they don't seem to have any padding. I've also added a Derived Column transform that trims the input data stream - no luck.

Any thoughts?

 

[FlaBusInt]

Never mind.

I found the "feature" where padded varchar() strings will not match with a CacheType of Full, but will match with Partial or None.

I added some Derived Column transforms to trim the columns in the two tables as I import them, and it seems to be working.

I guess if it weren't for all these undocumented features, anybody could do my job...

 

[MDXer]

it may actually be worth writing a custom script task to do this.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck