DT_NTEXT columns in an SSIS package and Truncation Warnings

Question

I have 2 questions :

1) I have a DT_NTEXT column whose value I'm extracting from the database and trying to feed into a Test column in Excel. I'm using a data conversion step in between to convert this to DT_TEXT (the unicode text version).

Still, in the end, Excel Connection Manager is writing ZERO rows (it's showing truncation warnings, but executing without error). I've even added a data viewer along the data flow with the following steps :

"In the arrows connecting your Source task to Data Conversion, and Data Conversion to Destination task , double click and you'll open the Data Flow Path Editor. Click on Data Viewer, then Add and click OK. That will allow you to see what is moving through the pipeline."

I'm not seeing any difference when I run my package though.

2) Is it possible for me to convert a DT_NTEXT column to a DT_WTSR column. In a perfect scenario, i should get this column into a standard nvarchar column in my Excel.

Answer 1

Try using DataType LongText. After placing the Excel Destination on Data Flow surface, join the source to the destination. Click on New for Name of the Excel sheet. Let the system decide the data type and see if that resolves your issue.

This is what I did that helped me come to this conclusion:

1. Create a table

   CREATE TABLE [dbo].[ntext]
   (
       [ID] [int] NULL,
       [NTEXT] [ntext] NULL
   ) ON [PRIMARY]
   

2. Insert some records

   1   asdsadsad
   2   qweqerrq
   3   But now, looping through the 351 flat-files to load the voter history records for the State's cities and towns, the package may process a few files, or up to 20 at a pop, before failing. I've been executing the packages interactively.  This is the text from the Output window from the last run:
   4    jus/juice, kilogramme/kilogram, lampe/lamp, mére/mother, nuit/night, océan/ocean, pantalon/pants, quintuplé/
   5   مباشر‎ - استمع مباشرة إلى راديو بي بي ...‎ - أخبار الشرق الاوسط‎ -
   6        Do not know what it says... just copied from some arabic web page from BBC site
   

3. Create a test package with a DFT.

   OleDB Source = the table you created

   Excel Dest = Create a new OLEDB connection manager

   Data access mode = Table or View

   Name of the excel sheet ... Select New...