Lookup Transformation or Slowly Changing Dimension?

  • I have an SSIS 2008 package that imports data from an Oracle database into a SQL Server database. Both source and destination tables have identical structures. The tables have a compound key consisting of INVENTORY_ITEM_ID and ORGANIZATION_ID. Here are the scenarios:

    1. If the row is in the destination but not the source, then do nothing.

    2. If the row is in the source AND the destination, then UPDATE the row in the destination table.

    3. If the row is in the source BUT NOT the destination, then INSERT that row into the destination table.

    I was considering using a Lookup transformation to make this work. However, this situation sounds like a type 1 Slowly Changing Dimension. So which transformation should I use, SCD or Lookup?

  • Well you can achieve this with both ways... but Slowly Changing Dimension is preferred.!

    :rolleyes:

  • The Slowly Changing Dimension worked very well, thanks!

  • You might want to implement the Type1 SCD Transformation:-)

    Raunak J

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply