November, 2008

A new books online entry has been posted which contains links for various upgrade resources. They were posted to coincide with the release of the massive SQL Server 2008 Upgrade Technical Reference Guide white paper, which should contain everything you...

This is just a place holder. Please see the post on different approaches of doing Range Lookups in SSIS .

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 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...

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...

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...

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...

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...

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...

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 Team Development Edition and Team Suite versions of Visual Studio 2008 comes with a performance profiling feature that can used to find bottlenecks in your custom extensions (tasks, data flow components, etc) for SSIS. Although having your extension...