This pattern has been out there for so long I had contemplated taking it out of my Performance Design Patterns talk, but it looks like it still managed to help some people at SQL Saturday #71 last weekend. This pattern is straight forward, and especially important since the data flow threading improvements...

This sample creates a data flow package with an OLEDB Source component feeding into a Lookup Transform. The Lookup transform is set to Full Cache mode, and uses [DimCustomer] as its reference table. Items of interest: CustomerKey and GeographyKey are used as the index (join) columns. This is configured...

Performing range lookups (i.e. to find a key for a given range) is a common ETL operation in data warehousing scenarios. It's especially for historical loads and late arriving fact situations, where you're using type 2 dimensions and you need to locate the key which represents the dimension value for...

The Cache Transform isn't currently able to update (i.e. append to) an existing persistent cache file. This pattern presents a way to incrementally build up your lookup cache if your data flow process is responsible for adding new rows to your reference table. For an alternative approach, please see...

The Lookup Transform does case sensitive string comparisons. This means that you need to a little bit of special handling to get it to work in a case insensitive way. In most cases (and especially if you're using a case sensitive collation on the database/table that holds your reference data), you'll...

The cascading lookup pattern uses two lookup transforms with different cache modes . A common use of this pattern is when your data flow is inserting new rows into your reference table. The first lookup in the chain is set to Full cache mode. Since it creates its cache before the data flow begins, it...

This is a pretty basic pattern where we use a lookup to determine whether we need to update and existing row, or insert a new one. The lookup checks if a key or set of values exists. If the key isn't found, the row is sent to an OLEDB Destination for the insert. If it is found, it is sent to an OLEDB...

This pattern is used when you have transformation logic which relies on a key which might not already exist. If the lookup fails to find the key, a new key is generated with a script task so it can be used later on downstream. Optionally, the key could be inserted immediately into the reference table...

From the Lookup presentation I put together for the MS BI conference in October, here is a series of posts which describe different patterns for using the Lookup transform. Key Generation Upsert Cascading Case Insensitive Incremental Cache Update Range Lookups Each pattern will contain a diagram with...

Using the Cache Connection Manager (CCM) is a new option for the Lookup transform in SSIS 2008. The CCM provides an alternative to doing lookups against a database table using an OLEDB connection. This post will suggest some best practices for using the Cache Connection Manager, and illustrate a couple...

Good news - a couple of Information log events where added to the Lookup Transform in SQL 2008 to help you better understand your lookup cache. When you're running in Full Cache mode , the message will tell you the number of rows in the cache, its total size, and how long it took to create it. When running...

Over the past couple of months I've been putting together a presentation on the Lookup Transform. I presented most of it as a Chalk Talk at the MS BI Conference last week, and from the evaluation scores, it seems like it was pretty well received. I'll be splitting up some of its content into a series...

On Monday (October 6th) I’ll be doing a chalk talk presentation at the MS BI Conference . The topic is Advanced Scenarios with the Lookup Transform . Here is the abstract: Performing lookups is one of the most common operations in the ETL process, and doing them incorrectly can severely affect the performance...

The lookup transform has a new property in SQL 2008 which controls how to handle rows with no matches – NoMatchBehavior. It has two values - “Treat rows with no matching entries as errors” and “Send rows with no matching entries to the no match output”. From the BOL entry: When the property is set to...

I’ve been invited to speak at the SSWUG BI Virtual Conference in September. Like John Welch mentioned in his blog, the current speaker lineup is very impressive. I’m honored (and a little intimidated) to be on the presenters list! I’ll be flying out to Tucson in early September to record three SSIS related...

Here's a quick tip from a couple other members of the SSIS team, Da Lin and David Noor. If you have a lookup component using partial cache mode using a Visual FoxPro oledb provider to connect to your reference table, be sure to change the OLE DB Services setting in the connection manager to something...

Just a quick post to link to an SSIS article posted on the sqlperf blog - Getting Optimal Performance with Integration Services Lookups