title: Why Data Type DT_NTEXT from SQL Command OLE DB Source?
body: <p>In our SSIS package, in the Data Flow OLE DB Source Editor, the Connection Manager is a SQL Command, and the SQL Command text is this:</p>
  
  <pre><code>With Managers AS (
    select distinct t1.ID,
      STUFF((SELECT distinct ' &amp; ' + t2. MgrName
        from myTableA t2
        where t1.ID = t2.ID
          FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)') 
        ,1,3,'') MgrName
    from myTableA)
  select Managers.MgrName as ManagerName
  :
  FROM myTableB INNER JOIN myTableA 
  ON myTableB.ID = myTableB.ID
  </code></pre>
  
  <p>When I right click on the OLE DB Source > Show Advanced Editor > Input and Output Properties, ManagerName’s DataType is Unicode text stream [DT_NTEXT].</p>
  
  <p>Why is the DataType DT_NTEXT, and not DT_WSTR or DT_STR ?</p>
  
  <p>The OLE DB Destination (SQL Server database) data type for this is a VARCHAR, so before I can populate the OLE DB Destination I need to do a “Data Conversion” from </p>
  
  <pre><code>Unicode text stream [DT_NTEXT]
  To 
  Unicode string [DT_WSTR]
  To
  string [DT_WSTR]
  </code></pre>