insert to .XLSX fails if len(column) > 255

Question

Post reply

insert to .XLSX fails if len(column) > 255

Viewing 6 posts - 16 through 20 (of 20 total)

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

polkadot SSChampion Points: 13664 More actions · Answer 1

I also tried to write sql to csv file and then csv to excel, doesn't help.

--Quote me

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

As I mentioned before OpenXML is an option. Didn't tried with larger contents but likely to work.

a tool that implements OpenXML and can be used from SSIS (in a script) is EPPlus ( http://epplus.codeplex.com/)
example here on how to use it http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/105432/

you could use OpenXML directly - but EPPlus makes it easier to code.

Edit: Did a quick pure OpenXML package.

Requirements
Install OpenXML 2.9.0
nuget.exe install DocumentFormat.OpenXml -Version 2.9.0

Register DLL's onto gac

Add an assembly to the GAC with gacutil.exe: https://emendezrivas.wordpress.com/2011/03/19/add-an-assembly-to-the-gac/
Add an assembly to the GAC with GAC Manager: https://gacmanager.codeplex.com/
Add an assembly to the GAC with WinGAC: https://wingac.codeplex.com/

SSIS script must add a reference to DocumentFormat.OpenXml

Atatched zip file contains a sample SSIS solution/package reads from a SQL server and creates a Excel file

Credits for the code Masud Ahmed - https://masudprogrammer.wordpress.com/2017/05/25/ssis-c-create-excel-file-worksheet-header-row-dynamically/
Most of the code is based on the above - with some fixes due to xml errors on this code, and some minor changes on my part.

Script to create the tables used on the SSIS also attached. change as required.

Database Connection string variable on the package will need to be changed.

Note that the code is quite basic and doesn't perform any type of formatting, neither does it specify the row/column we wish to copy the data to - this can be done by the developer.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

There is a way to do this that allows for any length of text in a cell. One only has to unzip the OOXML, parse it, load into a table and then use a dynamic query to present it.
😎

The only caveat is that one has to know the ISO/IEC 29500 standard inside out, the good thing is that once it is there, one can ingest at least 17 different types of spreadsheets that are based on the standard, here is my latest compilation:
1.    Microsoft Office 2007
2.    SoftMaker Office 2010
3.    LibreOffice
4.    OpenOffice.org
5.    Apache OpenOffice
6.    The Go-oo fork of OpenOffice
7.    KOffice
8.    Calligra Suite
9.    NeoOffice
10.    TextEdit
11.    iWork
12.    IBM Lotus Notes
13.    Abiword
14.    WordPerfect
15.    Kingsoft Office
16.    Google Docs
17.    SoftMaker FreeOffice

polkadot SSChampion Points: 13664 More actions · Answer 4

frederico_fonseca could you add a value to your test table description column (varchar 4000) exceeding 255 characters and tell me if the script task inserts into XLSX successfully?
I"m using VS 2013 and your ssis sln doesn't open up properly, but I get the general idea with the links provided. Would you kindly just check if the pkg completes insert into XLSX successfully with >255 value in one of those columns?

--Quote me

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

polkadot - Monday, March 18, 2019 2:05 AM
frederico_fonseca could you add a value to your test table description column (varchar 4000) exceeding 255 characters and tell me if the script task inserts into XLSX successfully?
I"m using VS 2013 and your ssis sln doesn't open up properly, but I get the general idea with the links provided. Would you kindly just check if the pkg completes insert into XLSX successfully with >255 value in one of those columns?

if you look at the script that populates the table you will find that one of the columns is getting a value higher than that already, and it did work as expected.

in any case use the attached - converted to work with VS2013

polkadot SSChampion Points: 13664 More actions · Answer 6

Hi frederico_fonseca
in the zipped folder 'ssisopenxml' i found
(1) sql script for two tables (I created both in a DB called TestDB )
(2) .dtsx with script task in control flow (and automatically associated .sln and .dtproj)
(3) ssisopenxml.database ( ? )

Questions:
What is (3) for?
I don't see any references in the script task to the database objects. What modifications to make in script task to pull test data from TestDB.dbo.openxml_test_data and into excel located at addresses specified in openxml_test table?
Are no connection managers needed?
In openxml_test, what is column [WorkbookNamePostfix] ? and what's difference between[DataProcessingDirectory] vs. [FilePath] ?

--Quote me