Browse by Tags

Tagged Content List
  • Blog Post: Pattern: Replacing NULL values with a Derived Column

    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...
  • Blog Post: 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 used as the index (join) columns. This is configured...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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 is found, it is sent to an OLEDB...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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 size, and how long it took to create it. When running...
  • Blog Post: 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 be splitting up some of its content into a series...
  • Blog Post: 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 doing them incorrectly can severely affect the performance...
  • Blog Post: 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”. From the BOL entry: When the property is set to...
  • Blog Post: 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 Tucson in early September to record three SSIS related...
  • Blog Post: 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 setting in the connection manager to something...
  • Blog Post: Getting optimal lookup performance

    Just a quick post to link to an SSIS article posted on the sqlperf blog - Getting Optimal Performance with Integration Services Lookups
Page 1 of 1 (17 items)