Search code examples
ssisdata-warehousestar-schema

SSIS Surrogate Key incrementation

I'm using SSIS to create a star schema for a data warehouse with surrogate keys (sg).

My process goes like this:

  1. find max sg (using SQL)

  2. in data flow: data source-> c# script that adds +1 to the max sg -> write to destination.

Now, with fixed dimensions it works without problems. Every added row gets the sequential sg.

However when I use the Slowly Changing Dimension and historically updating a row I get the following:

sg_key  |  name | city | current_row
1       |  a    |  X   | true
2       |  b    |  Y   | true
3       |  c    |  Z   | false
4       |  d    |  H   | true
7       |  c    |  T   | true

Now, correct me if I'm wrong but I always thought SSIS is pushing one row at a time through all the flow tasks, but it looks like it first generates ALL the sg_keys for all the rows and then sends the updated row through the flow.

Do I understand how SSISworks in a wrong way? How can I fix it?

Cheers, Mark.

Solution

  • If you use SQL Server as a destination, why didn't use an IDENTITY Column? (instead of a C# script)

    https://msdn.microsoft.com/en-us/library/ms186775.aspx

    Identity will automatically increment your column when you insert a new row. If you don't update this column, the value will not change.

    Arnaud