title: Cannot convert between unicode and non-unicode string data types in SSIS
body: <p>I have set up a SSIS solution where I use SQL Server to import the 'raw data' and convert these into suitable data types and save these into a table. For the column I have problem with I have done like this (note I only want the date, not the time value which is why I use <code>112</code> as an parameter):</p>
  
  <pre><code>SELECT CONVERT(nvarchar(15),a.DeliveryDate,112) AS DeliveryDate
  INTO MyNewTable
  FROM MyRawDataTable
  </code></pre>
  
  <p>I then use  <code>MyNewTable</code> as an input to my SSIS in Visual Basic. In Data Flow I right click my Source OLE DB and ensure that the datatype is in fact <code>DT_WSTR</code> of length <code>15</code> under <code>Advanced Options</code> and <code>Input and Output datatypes</code>. </p>
  
  <p>When I try to run my SSIS I get the error </p>
  
  <blockquote>
    <p>Cannot convert between unicode and non-unicode string data types</p>
  </blockquote>
  
  <p>Which I figured may have to do with the fact that <code>a.DeliveryDate</code> sometimes contain NULL values. I therefor adjust my code in the <code>SQL server</code> into: </p>
  
  <pre><code>SELECT 
  CONVERT(nvarchar(15),COALESCE(a.DeliveryDate,'1900-01-01 00:00:00.000'),112) AS DeliveryDate
  INTO MyNewTable
  FROM MyRawDataTable
  </code></pre>
  
  <p>and subsequently run my SSIS. I notice that I still have <code>DT_WSTR</code> of length <code>15</code> in my Output from the OLE DB import in SSIS. This will also cause the same error though. </p>
  
  <p>How can I get my program to work? What I have tried is to convert the datatype to varchar in my SQL table, to use COALESCE(...,0) instead of using the year 1900 as a dummy value. </p>
  
  <p>Please note the solution <strong>cannot</strong> contain <em>Data conversion transformation</em> tasks in the Data Flow of SSIS. </p>
  
  <p><strong>EDIT:</strong>
  I have done the same conversions for another date, which I got from the same raw data file. For this date, this works without any hinch. My conclusion from this is that, there might be a problem when <code>DeliveryDate</code> contain NULL values (I do not need to use COALESCE for the date that works, as it Always contain a valid datetype). </p>
  
  <p><strong>EDIT 2:</strong> 
  I updated my SQL table <code>MyNewTable</code> to not include any rows where <code>a.DeliveryDate</code> had the value <code>NULL</code>. This however caused the same issue. </p>
  
  <p><strong>UPDATE</strong>
  I noticed that the appearence I want to have after my conversion <code>yyyymmdd</code> for the month is in fact ISO. I therefore added a CAST after my CONVERSE in order to get a legit input to SSIS. Like this:</p>
  
  <p><code>CAST(Convert(nvarchar(15),a.DeliveryDate,112) AS nvarchar(15)) AS DeliveryDate</code></p>
  
  <p>This causes my import from the OLE DB source in SSIS Date Flow to not cause error. However error still occurs at my export to my Datewarehouse complaining about datatypes. Even though it still says that the datatype is DT_WSTR in every step in my data flow. </p>
  
  <p>Also, what is weird, is that when I import from OLE DB using a table it causes error. Using the same query I create my table with as an import (using the query as an input that is) causes no issues. </p>