CODEWITHSUNDEEP
sql-serverssisetlssis-2012ntext
faujong
faujong

Reputation: 1129

Why Data Type DT_NTEXT from SQL Command OLE DB Source?

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]

Upvotes: 0

Views: 3797

Answers (1)

Thom A
Thom A

Reputation: 95534

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.

Upvotes: 3

Related Questions