SSIS Lookup Transformation Quirk

After sometime now using SSIS to load data into other systems such as Microsoft CRM and Oracle Hyperion, I have finally started down the path towards building my first Data Mart using SSIS.

At this stage I am really looking at what is going to work best so I have built some dimensions and now I am loading a fact with ‘actuals’ taken from the Navision General Ledger.

I am attempting to follow the Kimball method, and in loading the data I am performing lookups using the Lookup Transformation on the dimensions to retrieve the surrogate keys.  Standard stuff so far!

I have one particular dimension where the natural key is of type varchar(20). The corresponding column in the data stream is also of type varchar(20). The data loads with some lookup errors all of the same string – ‘STT2003’.  Okay, so it must be missing from the dimension. Imagine my surprise when I find that ‘STT2003’ is in the dimension!

So I checked the types – both varchar(20). I checked the string length on the lookup in case there were any non-printing characters but nothing. I applied the ltrim and rtrim functions to the source data but still no luck. Fortunately, I do not have a lot of hair to pull out. So I search the MSDN forum for SSIS and I saw a reference to a similar problem which referred to string length. But, I had already done that – or had I ?

So I applied the ltrim/rtrim functions to the natural key in the lookup and it worked. It seems that the key had acquired extra characters in the lookup.

Stay tuned for the next installment.