How to make DT_NTEXT work in Excel destination in SSIS

• hema_subhash

  Old Hand

  Points: 345

  More actions

  June 17, 2011 at 3:08 am

  #242202

  Hello everyone,

  I have been trying to create an SSIS 2008 package that populates the results of a stored procedure in MS SQL server 2008 into different worksheets of an Excel 2007 file.

  The problem is that there are many columns that have been declared as NVARCHAR(MAX) in the DB and therefore need more than 255 characters (max characters allowed for DT_WSTR) to prevent any truncation at the excel destination. I have been trying to use a Data Conversion Task to cast it to a DT_NTEXT type, but my excel destination fails when try to do this.

  The errors are:

  Error: 0xC0202009 at Clarification Report, Excel Destination 1 [164]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

  Error: 0xC0202025 at Clarification Report, Excel Destination 1 [164]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

  Error: 0xC004701A at Clarification Report, SSIS.Pipeline: component "Excel Destination 1" (164) failed the pre-execute phase and returned error code 0xC0202025

  As soon as I remove the DT_NTEXT field and replace it with a DT_WSTR data type, the issue is resolved but there are some records where the data is truncated due to this.

  Is there any way I can make the excel destination work with the DT_NTEXT data type?

  Please help.

  Thanks in advance.

  Hema

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  June 17, 2011 at 9:19 am

  #1340522

  This thread has the answer in it:

  http://www.sqlservercentral.com/Forums/Topic349843-148-1.aspx

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato