title: SSIS ISNULL to empty string
body: <p>So I am currently working on a migration from an old Advantage database server to SQL 2005 using SSIS 2008. One of the columns in the old Advantage database is a MEMO type. By default this translates to a DT_TEXT column. Well in the new database I do not need this large of field, but can limit it to something such as VARCHAR(50). I successfully set up a derived column transformation to convert this with the following expression:</p>
  
  <pre><code>(DT_STR,50,1252)[ColumnName]
  </code></pre>
  
  <p>Now I want to go a step further and replace all NULL values with an empty string. This would seem easy enough using an <code>ISNULL([ColumnName])?"":(DT_STR,50,1252)[ColumnName]</code> expression, but the problem is that the OLE DB Destination contains the following error </p>
  
  <blockquote>
    <p>Cannot convert between unicode and non-unicode strings...</p>
  </blockquote>
  
  <p>So apparently the whole ISNULL expression converts the data type to Unicode string [DT-WSTR]. I have tried a variety of casts upon the whole expression or different parts, but I cannot get the data type to match what I need it.</p>
  
  <p>First, is it possible to convert the DT_TEXT type directly to unicode? From what I can tell, the casts don't work that way. If not, is there a way to get an expression to work so that NULL values get converted to empty strings?</p>
  
  <p>Thank you for all your help!</p>