title: Transforming source columns to create unique destination column in SSIS
body: <p>I have been building an SSIS package that transfers data between two databases with different schemas. In the destination database one column has a unique key constraint, and the data needs to be populated from 2 source database columns like:</p>
  
  <pre><code>SELECT (LOWER(LEFT(Col1.Column1, 5)) + LOWER(LEFT(Col1.Column2, 5)))
  </code></pre>
  
  <p>i.e. The first 5 characters from each column, plus an incrementing number at the end if there are duplicates.</p>
  
  <p>The incrementing number has to be dependent on the first 10 characters so every different variation has its own incrementing number. Ex:</p>
  
  <p><strong>Dest.Column</strong><br>
  apapapap1<br>
  apapapap2<br>
  apapapap3<br>
  epepepep1<br>
  epepepep2  </p>
  
  <p>This package is also run every week, so adding the necessary column to the SSIS OLEDB source won't work.</p>
  
  <p><strong>So my question is:</strong> Is it possible to carry out this transformation in SSIS before writing to the destination database, and if so, how?</p>