Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

All Tags » Lookup   (RSS)
Friday, January 02, 2009 10:34 AM

API Sample – Lookup Transform

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
Posted by mmasson | 3 Comments
Filed under: , ,
Tuesday, November 25, 2008 9:30 AM

Lookup Pattern: Range Lookups

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
Posted by mmasson | 11 Comments
Filed under:
Sunday, November 23, 2008 12:16 PM

Lookup Pattern: Incremental persistent cache updates

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
Posted by mmasson | 2 Comments
Filed under:
Sunday, November 23, 2008 9:53 AM

Lookup Pattern: Case Insensitive

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
Posted by mmasson | 2 Comments
Filed under:
Saturday, November 22, 2008 8:46 PM

Lookup Pattern: Cascading

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
Posted by mmasson | 2 Comments
Filed under:
Saturday, November 22, 2008 8:31 PM

Lookup Pattern: Upsert

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
Posted by mmasson | 2 Comments
Filed under:
Saturday, November 22, 2008 8:28 PM

Lookup Pattern: Key Generation

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
Posted by mmasson | 4 Comments
Filed under:
Saturday, November 22, 2008 8:28 PM

Lookup Patterns

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
Posted by mmasson | 1 Comments
Filed under:
Saturday, November 22, 2008 7:51 PM

Lookup - Using the cache connection manager

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
Posted by mmasson | 3 Comments
Filed under:
Saturday, October 18, 2008 4:01 PM

Calculating the size of your Lookup cache

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
Posted by mmasson | 2 Comments
Filed under:
Saturday, October 18, 2008 1:54 PM

Lookup cache modes

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
Posted by mmasson | 7 Comments
Filed under:
Wednesday, October 01, 2008 2:27 PM

Presenting at the Microsoft BI Conference

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
Posted by mmasson | 3 Comments
Filed under: ,
Friday, August 22, 2008 4:17 PM

Enum value for Lookup’s NoMatchBehavior property

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”.
Posted by mmasson | 0 Comments
Filed under:
Wednesday, July 30, 2008 2:00 PM

SSWUG Business Intelligence Virtual Conference

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
Posted by mmasson | 1 Comments
Monday, March 03, 2008 9:20 PM

Lookups with Visual FoxPro

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
Posted by mmasson | 1 Comments
Filed under:
More Posts Next page »
 
Page view tracker