title: How to assign a default value to NULL dates using SSIS derived column transformation?
body: <p>In my SQL 2008 SSIS environment, I have data coming from a CSV file and I need to fix up null dates.</p>
  
  <p>Data from CSV looks like this:</p>
  
  <pre><code>01011990
  01011990
  01011990
  01011990
  01011990
  </code></pre>
  
  <p>In my staging table, I have a column defined as:</p>
  
  <pre><code>[SHIPPED DATE] date NOT NULL
  </code></pre>
  
  <p>First I fix the date values by pushing the column through a derived transformation that has:</p>
  
  <blockquote>
    <p>(DT_DBDATE)(SUBSTRING([SHIPPED DATE],1,2) + "/" + SUBSTRING([SHIPPED DATE],3,2) + "/" + SUBSTRING([SHIPPED DATE],5,4))</p>
  </blockquote>
  
  <p>The above transformations makes my string dates coming form CSV import into a date column in my SQL db.</p>
  
  <p>The output of this is:</p>
  
  <pre><code>1990-01-01
  1990-01-01
  1990-01-01
  1990-01-01
  1990-01-01
  </code></pre>
  
  <p><strong><code>Next</code></strong>, I'd like to deal with NULL dates so that I can stay true to the "NOT NULL" definition in my db. What I want to do is assign the NULL dates a default value but I get an error message as per below:</p>
  
  <blockquote>
    <p>Error at CSV to SQL [Derived Column [24817]]: The data types "DT_WSTR" and "DT_DBDATE" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.</p>
  </blockquote>
  
  <p>My CSV column is WSTR, my destination in the SQL DB is a "date" column.</p>
  
  <p>My transformation I am trying to use is:
      ISNULL([SHIPPED_DATE]) ? "1900-01-01" : [SHIPPED_DATE]</p>
  
  <p>It does not want to work due to the above message. Can anyone point me to the right direction?</p>