title: SSIS Convert Blank Datetime column to NULL and Parse Input
body: <p>I have the following simple SSIS Package to process a comma delimited file:</p>
  
  <p><a href="https://i.stack.imgur.com/2R48n.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2R48n.png" alt="enter image description here"></a></p>
  
  <p>I'm having problems with date columns - specifically <code>MoveInDate</code> which the input column has data in the format <code>'20090731000000'</code>.</p>
  
  <p>It goes into a database column <code>MoveInDt</code> which has a Datetime data type and data ends up in the format: </p>
  
  <pre><code>'2009-07-31 00:00:00.000'
  </code></pre>
  
  <p>In SSIS I have specified the input column to be <code>DT_DBTIMESTAMP</code>.  I'm unable to figure out how to do a simple 'if MoveInDate is blank, load null, else change data to 'YYYY-MM-DD 00:00:00.000'</p>
  
  <p>Right now I can't even seem to figure out how to load a hardcoded date</p>
  
  <p>This fails</p>
  
  <p><a href="https://i.stack.imgur.com/hoN4W.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hoN4W.png" alt="Derived Column"></a></p>
  
  <p>As does </p>
  
  <pre><code>"2019-01-01"
  </code></pre>
  
  <p>Or</p>
  
  <pre><code>(DT_DBTIMESTAMP)"2019-01-01"
  (DT_DBTIMESTAMP)(DT_DATE)"2019-01-01"
  </code></pre>
  
  <blockquote>
    <p>Error: 0xC02020A1 at Data Flow Task, Flat File Source [11]: Data conversion failed. The data conversion for column "Move In Date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".<br>
    Error: 0xC0209029 at Data Flow Task, Flat File Source [11]: </p>
  </blockquote>
  
  <p>Looking up that message I've found posts about the data types not matching on the input to output column, but looking at it ...</p>
  
  <p>Checking the flat file input column, it is defined as <code>DT_DBTIMESTAMP</code></p>
  
  <p><a href="https://i.stack.imgur.com/wwBhv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wwBhv.png" alt="enter image description here"></a></p>
  
  <p>And the destination column is also defined as <code>DT_DBTIMESTAMP</code> when looking at input output properties.</p>
  
  <p><a href="https://i.stack.imgur.com/k0k3q.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k0k3q.png" alt="enter image description here"></a></p>
  
  <p>I've looked at many questions here and elsewhere and have tried reducing the input to one simple row in the input file.  I've tried recreating the package several times.   </p>