Resources for Upgrading to SQL Server 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 need to know about upgrading from SQL 2000 or SQL 2005.


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 the key which represents the dimension value for a given point in time….


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 table. For an alternative approach, please see the Other Resources section at the…


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 that holds your reference data), you’ll want to add an…


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 creates its cache before the data flow begins, it will…


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 the insert. If it…


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 be inserted immediately into the reference table…


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 Lookups Each pattern will contain a diagram with Upstream and Downstream images. These represent any collection…


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 Cache Connection Manager, and illustrate a couple of common scenarios that the…