March 16, 2019 at 1:23 am
I also tried to write sql to csv file and then csv to excel, doesn't help.
--Quote me
March 16, 2019 at 3:39 am
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://qa.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.
March 16, 2019 at 6:28 am
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
March 18, 2019 at 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?
--Quote me
March 18, 2019 at 2:57 am
polkadot - Monday, March 18, 2019 2:05 AMfrederico_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
March 19, 2019 at 11:35 pm
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