I just answered to a request on SQLBI.EU forum of someone that is looking for a better lookup component, specifically designed for typical Data Warehouse scenarios.

While you can read the whole thread on the forum, I try to resume most wanted requirements, hoping that Katmai (next SQL Server version) developers are already working on that!

  • Default value for lookup failure: simple “dummy value” assignment for lookup that fails, so that we can easily implement fact table processing in non-incremental scenarios
  • Synchronous action for lookup failure: it is absolutely necessary to have the chance to execute a script (or another component, but this seems harder) in a synchronous way against the processed row. The need for synchronization is that if I need to add a dimension member, I need to do that *before* to process the next fact table row, that can reference the same member and I don’t want (to try) to create a second surrogate key for the same application key
  • Dynamic update at preloaded lookup table: the previous feature brings the need for a in-memory update of the lookup table initially loaded to get fast performance. This thing has to be done synchronously to the row process and it is necessary only to add elements (no delete/update required).

Having lookup source, it should be not so hard to obtain these features: 80% of functionality are already implemented. Critical point is the need to make operations synchronous to the row process, before to process another row.

Actually I’m doing that with a double lookup: the first try to lookup in memory for existing (at start time) dimension members, the second do the lookup calling a stored procedure that first try to lookup with a SELECT sql statement, and if it doesn’t get the value, it INSERTs a new dimension member. That the better way I found until now to implement this pattern in a fast and readable way, but I really dislike to have to add a stored procedure to support my SSIS package (I know that my position could be controversial and it could be an argument for a future post!).