title: SSIS Combine Multiple Columns into one with nulls
body: <p>I know similar questions have been asked before, however I have a unique case that is not solved by the previous answers.</p>
  
  <p>I have a Database rows that has 5 comment columns that I would like to combine into one.
  Example:</p>
  
  <pre><code>    ID     NAME    CMT1     CMT2    CMT3    CMT 4   CMT5
      1    John     BLAH     BLAH    null    null    null
      2    Mary     BLAH     null    null    null    null
      3    Sue      BLAH     BLAH    BLAH    BLAH    BLAH
  </code></pre>
  
  <p>I would like to see the following:</p>
  
  <pre><code>    ID      NAME    CombinedComment
      1       John    BLAH BLAH
      2       Mary    BLAH
      3       Sue     BLAH BLAH BLAH BLAH BLAH
  </code></pre>
  
  <p>Now for the caveats.</p>
  
  <pre><code>1. I cannot use SQL to join the data. The data comes from a linked server via a stored procedure. This is the usual response to this problem.
  2. I cannot modify the stored procedure.
  3. I am using a Derived Column and using the following:
  
      Combined      CMT1 + " " + CMT2  + " " + CMT3  + " " + CMT4  + " " + CMT5
  
  This works only for rows that have a value in ALL 5 comment fields. 
  </code></pre>
  
  <p>So to summarize, I would want the Combined Comment column to work for all rows even if there is null in the other comment fields.</p>