title: Add incremental number in duplicate records
body: <p>I have SSIS package, which retrieves all records including duplicates. My question is how to add an incremental value for the duplicate records (only the ID and PropertyID).</p>
  
  <p>Eg</p>
  
  <p>Records from a Merge Join</p>
  
  <pre><code>ID   Name   PropertyID  Value
  1    A      1           123
  1    A      1           223
  2    B      2           334
  3    C      1           22
  3    C      1           45
  </code></pre>
  
  <p>Now I need to append an incremental value at the end of the each record as</p>
  
  <pre><code>ID   Name   PropertyID  Value  RID
  1    A      1           123    1
  1    A      1           223    2
  2    B      2           334    1
  3    C      1           22     1
  3    C      1           45     2
  </code></pre>
  
  <p>Since ID 1 &amp; 3 are returned twice, the first record has RID as 1 and the second record as 2.</p>
  
  <p>ID and PropertyID need to be considered to generate the Repeating ID i.e RID.</p>
  
  <p>How can I do it in SSIS or using SQL command?</p>
  
  <p>Update #1:</p>
  
  <p><a href="https://i.stack.imgur.com/mqSwD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mqSwD.png" alt="enter image description here"></a></p>
  
  <p>Please correct me if I'm wrong, since the data is not stored in any table yet, I'm unable to use the select query using rownumber(). Any way I can do it from the Merge Join?</p>