Convert Natural to Surrogate Key


  • Hi,

    I am creating a sales data mart and now I am working on the ETL process. In my dimension tables I have the natural and surrogate key, the last one is my primary key. My fact tables relates to these dimensions using the surrogate key.

    During the load process I need to convert the natural to surrogate key. How can I do that using Integration Services? Is there any data flow transformation where I can use an input column to lookup my dimension table using the natural key and then output the surrogate key?

    Thanks for any help!


    Monday, June 22, 2009 1:27 PM


  • The most common approach, and probably the most recommended one, is to use a lookup transformation against each of the dimension tables. For performance reasons, make sure you provide a query (not a table name from the drop down list) to specified only the columns you need for the operation (likely only the surrogate and natural key columns). If you can, laso include a where clause to reduce the number of rows (e.g. if you have are interested only in 'current' dimension rows)
    Rafael Salas | Don’t forget to mark the post(s) that answered your question
    Monday, June 22, 2009 1:54 PM