Export nvarchar(4000) to Excel

  • I'm trying to use the SSMS 18.7.1  Export Wizard to export to an Excel 2010 xlsx file.  Per this https://docs.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-ver15  you should have "first few rows" with > 255 characters in that field.  I can see that in the mapping to Excel it will create a data type as LongText for it.  During run I get:

    Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

    An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80040E21 Description: "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.".

    (SQL Server Import and Export Wizard)

    The source table is :

    CREATE TABLE [dbo].[wh_comments](

    [comments] [nvarchar](4000) NULL

    ) ON [PRIMARY]

    I downloaded and install the redistributable from above link as well.

  • Check the data source task. Right Click --> Open Advanced Editor. On Input/Output tab open External Columns and Output Columns. Find your column and see what the datatype and length is. S/b Unicode string with len of 4000. if not change it and look at the other objects in data flow to make sure it is the same. Also, look at connection manager to make sure it's not set there to 255

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply