SQL Server 2014 : can't export Varchar(1000) in a query to Excel 2007. Works for Excel 97-2003

3

I am working with SQL Server 2014. I have applied the Microsoft Access Database Engine 2010 Redistributable to my server. I have tried both the 32-bit and 64-bit drivers. With the 32-bit driver, I can export to Excel 97-2003 without a problem. If I try to export to Excel 2007, my Varchar(1000) column fails with the following error:

Error 0xc0202009: Data Flow Task 1:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x00040EDA.
An OLE DB record is available.
Source: "Microsoft 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)

If I go with Varchar(255) instead of Varchar(1000), it works fine in both versions of Excel. The column maps from Varchar(1000) to Longtext and I set it to ignore truncation errors.

I have run into this with various projects, this project requires the full length of the column and truncation is not accepted. I have tried running the wizard from SMSS and from the Start menu (both 32-bit and 64-bit, although it seems SMSS runs the 32-bit version if the 32-bit driver is installed).

I could probably stick with Excel 97-2003 for a while, but eventually it will go over 65,536 rows and will have to go to Excel 2007. I cannot export to csv, as my client requirements are that it be placed into an Excel file. They would rather have .xlsx files, but will accept .xls files for the time being.

The export needs to be run on a regular basis, so I need to save the package as a .dtsx file.

Does anyone have any ideas on how to fix this?

export-to-excel
sql-server-2014
asked on Stack Overflow Aug 3, 2016 by Fred • edited Aug 3, 2016 by marc_s

1 Answer

0

One of the workarounds I found on a website sais:

You have to include the account you use to execute the package in the performance counters group.

But I don't know how to apply this in SSIS, I will look for this and try to update my question.

answered on Stack Overflow Nov 23, 2016 by Tassisto

User contributions licensed under CC BY-SA 3.0