title: SSIS Surrogate Key incrementation
body: <p>I'm using SSIS to create a star schema for a data warehouse with surrogate keys (sg).</p>
  
  <p>My process goes like this: </p>
  
  <ol>
  <li><p>find max sg (using SQL)</p></li>
  <li><p>in data flow: data source-> c# script that adds +1 to the max sg -> write to destination.</p></li>
  </ol>
  
  <p>Now, with fixed dimensions it works without problems. Every added row gets the sequential sg.</p>
  
  <p>However when I use the Slowly Changing Dimension and historically updating a row I get the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Do I understand how SSISworks in a wrong way? How can I fix it?</p>
  
  <p>Cheers,
  Mark.</p>