title: SSIS 2008: Derived Column Transform a String to a Date Data Type
body: <p>I had this SSIS package working yesterday and now I'm getting this error with no changes overnight. </p>
  
  <p>Basically I'm getting a String that looks like: yyyymmdd, I need to transform it into a Date Data Type. So I take substrings, to get yyyy/mm/dd then cast it into a Date Type. </p>
  
  <p>The Path: </p>
  
  <p><strong>Flat File Source ---> Dervied Column ---> All my SSIS package intergration/insertion tasks</strong></p>
  
  <p>Here are the Expressions: </p>
  
  <pre><code>(DT_DATE)(SUBSTRING([PolicyExpire],1,4) + "/" + SUBSTRING([PolicyExpire],5,6) + "/" + SUBSTRING([PolicyExpire],7,8))
  (DT_DATE)(SUBSTRING([BirthDate],1,4) + "/" + SUBSTRING([BirthDate],5,6) + "/" + SUBSTRING([BirthDate],7,8))
  (DT_DATE)(SUBSTRING([DLIssueDate],1,4) + "/" + SUBSTRING([DLIssueDate],5,6) + "/" + SUBSTRING([DLIssueDate],7,8))
  </code></pre>
  
  <p>Here is the error: </p>
  
  <blockquote>
    <p>Error: 0xC0049064 at Extract EXD data
    from Flatfile into YD db 1, Derived
    Column [3352]: An error occurred while
    attempting to perform a type cast.</p>
    
    <p>Error: 0xC0209029 at Extract EXD data
    from Flatfile into YD db 1, Derived
    Column [3352]: SSIS Error Code
    DTS_E_INDUCEDTRANSFORMFAILUREONERROR. 
    The "component "Derived Column"
    (3352)" failed because error code
    0xC0049064 occurred, and the error row
    disposition on "input column
    "PolicyExpire" (3368)" 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.</p>
    
    <p>Error: 0xC0047022 at Extract EXD data
    from Flatfile into YD db 1,
    SSIS.Pipeline: SSIS Error Code
    DTS_E_PROCESSINPUTFAILED.  The
    ProcessInput method on component
    "Derived Column" (3352) failed with
    error code 0xC0209029 while processing
    input "Derived Column Input" (3353).
    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.</p>
  </blockquote>