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.



4 thoughts on “SSIS Lookup Transformation Quirk

  1. Hi Micheal
    Came accross your blog while seaarching for SSIS help documents on google..
    Its pretty interesting.. And keep them coming

    I have been working on informatica.. and new SSIS.
    I have been looking for some help on Data transformations.. If you have any PDF or url, can you please share them with me.

    Thanks and Regards

  2. 3 years later this is still a problem, I need what seems like a completely useless RTRIM(LTRTIM()) of a varchar with no real padded spaces.

  3. Michael,

    Thanks for the post. Is there any reason you are using the Lookup Transformation instead of staging the data on the same server and simply outer joining to figure out what is present and what is missing? I have found that the Lookup Transformation is somewhat of a memory hog even when I optimize the cache. Of course, if you have a beefy server with tons of memory to spare it may not matter.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s