How to get rid of trailing blanks without trim?

[ekareem]

I am reading data from a fixed lenght record with fixed length columns.
Even though I define each column as Varchar in the input Sequential stage, and as Varchar2 in Oracle target table, I still get trailing blanks.

Since I have so many columns in input, I don't want to explicitly call trim - Shouldn't Varchar take care of this implicitly?

Thanks.

[kcbland]

No.

You will need to trim any data that comes out of a fixed width file. Look into mass adding TRIM to every derivation on a transformer immediately after the source sequential file stage. If you open help on the Transformer, you'll see a column propagation ability, where you can highlight all of the derivations, right click the derivation propagation, and enter something like TRIM($1) which would mass add TRIM(...) around every derivation. That will fix your data coming off the fixed width stage.

I don't know how much you've read on the DSXchange, but we consistently remind people that it's up to you to fix data, that metadata down below really doesn't do anything to the data. You can map a 100K wide text column into a BIT column and there's no issue. It's only when forming fixed width files or talking to databases that the metadata really matters.

[ray.wurlod]

VARCHAR does not implicitly remove spaces - if you (or someone) puts them there, then they're there. VARCHAR only means that you don't have to fill the maximum width of the field.

Make use of derivation propagation, as Ken suggested.

[ekareem]

Thanks for your help.