title: Derived Column in SSIS - Trailing Space
body: <p>I have tried several ways to format a derived column to eliminate trailing spaces. To format the column when just viewing the column in Excel and transferring to Access was: </p>
  
  <pre><code>'=TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160),"")))
  </code></pre>
  
  <p>In the derived column I've tried:</p>
  
  <pre><code>- REPLACE(RTRIM([Provider Number]),"\\x00A0","") == "" ? (DT_STR,10,1252)NULL(DT_STR,10,1252) : [Provider Number]
  
  - TRIM([Provider Number]) == "" ? (DT_STR,10,1252)NULL(DT_STR,10,1252) : [Provider Number]
  
  - LTRIM(RTRIM([Provider Number]))
  </code></pre>
  
  <p>When I query the length for the column my results still count 1 extra space when the package runs (successfully):</p>
  
  <p><a href="https://i.stack.imgur.com/Y3Urz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Y3Urz.png" alt="Results"></a></p>