title: SSIS - Derived Column
body: <p>I am having an issue with using the Derived Column Transformation Editor and was wondering if anyone on here could help me out.</p>
  
  <p>I have a column called <code>WorkitemNumber</code> that holds information like <code>INC0000001234</code></p>
  
  <p>I want to change the <code>WorkitemNumber</code> to a more unique number to prevent overlapping entries in between months. So I came up with this...</p>
  
  <p>I have a Derived Column:</p>
  
  <pre><code>Derived Column Name: ConvertID
  Derived Column: &lt;add as new column&gt;
  Expression: REPLACE(WorkitemNumber,"INC","") + "913"
  Data Type: Unicode string [DT_WSTR]
  Length: 258
  </code></pre>
  
  <p>The expression above changes <code>INC0000001234</code> to <code>0000001234913</code>. What I would like to do is cut out all of the extra 0s, but I want to refrain from using LTRIM because it will break once the incident report numbers go to the next set of  thousands. </p>
  
  <p>I am pretty new with using SSIS and it would be really helpful if someone would be able to tell me how I would go about getting rid of the extra 0s on the end regardless of the amount of 0s in front of the incident number.</p>
  
  <p>I would want the final output to be <code>1234913</code> recardless if the incident number was <code>INC0000001234</code> or <code>INC001234</code>.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I was able to drop the 0s but I am getting the following error and the information is not being written into my other tables...</p>
  
  <pre><code>[SQL03 [459]] Error: SSIS Error Code DTS_E_OLEDBERROR.  
  An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  
  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
  
  [SQL03 [459]] 
  Error: There was an error with input column "ConvertIncidentID" (1015) 
  on input "OLE DB Destination Input" (472). 
  The column status returned was: "Conversion failed because the data value 
  overflowed the specified type.".
  
  [SQL03 [459]] 
  Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  
  The "input "OLE DB Destination Input" (472)" 
  failed because error code 0xC020907A occurred, and the error row disposition on 
  "input "OLE DB Destination Input" (472)" 
  specifies failure on error. An error occurred on the specified object of the 
  specified component.  There may be error messages posted before this with more 
  information about the failure.
  
  [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  
  The ProcessInput method on component "SQL03" (459) 
  failed with error code 0xC0209029 while processing input "OLE DB Destination Input" 
  (472). The identified component returned an error from the ProcessInput method. 
  The error is specific to the component, but the error is fatal and will cause the 
  Data Flow task to stop running.  There may be error messages posted before this with 
  more information about the failure.
  </code></pre>