SSIS Destination new table ntext

Question

Why when running an SSIS job and telling the destination editor to create a new table would it default certain text fields to NTEXT data type in the new table even though the source table for the field(s) in question is varchar?

Answer 1

My answer assumes you are using an ADO.Net data source and the fields in question are VARCHAR(MAX).

1. ADO.NET data sources convert strings to DT_WSTR (hence the N part of NTEXT). Retaining non unicode can be obtained by changing data flow source type to OLEDB or converting the data in a data conversion transformation.

2. This stackoverflow answer explains how SQL Server handles VARCHAR(MAX). So my understanding is when SSIS see's VARCHAR(MAX), it knows that if the data is larger than 8000 characters, it will need converting. As a result SSIS converts the data automatically to TEXT and lets SQL Server handle it inserting the data into a VARCHAR(MAX) destination.

Answer 2

because VARCHAR(x) and VARCHAR(MAX) are different SQL Server Types. Varchar(max) is treated by SQL Server like the TEXT data type.

That's why the varchar columns are string [DT_STR] and varchar (max) are text stream [DT_TEXT] on SSIS