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

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

    --Quote me

  • 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.

  • 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

  • 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

  • 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

  • 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

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

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