0

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:

With Managers AS (
  select distinct t1.ID,
    STUFF((SELECT distinct ' & ' + 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

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].

Why is the DataType DT_NTEXT, and not DT_WSTR or DT_STR ?

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 

Unicode text stream [DT_NTEXT]
To 
Unicode string [DT_WSTR]
To
string [DT_WSTR]
Hadi
  • 36,233
  • 13
  • 65
  • 124
faujong
  • 949
  • 4
  • 24
  • 40
  • 2
    Searching on google is easiest then asking on SO!! just search for `nvarchar(max) dt_ntext` and you will find a lot of relevant articles: https://www.mssqltips.com/sqlservertip/5047/import-and-export-varcharmax-data-with-sql-server-integration-services-ssis/ – Yahfoufi Feb 06 '19 at 08:54
  • You could cast `Managers.MgrName` to varchar in your source query, and then you could skip the data conversion task. – digital.aaron Feb 06 '19 at 16:21

1 Answers1

3

DT_WSTR and DT_STR have maximum lengths of 4,000 and 8,000 characters respectively. As a result they are too small to be mapped against an (n)varchar(MAX) and so DT_NTEXT and DT_TEXT are used.

Thom A
  • 88,727
  • 11
  • 45
  • 75