Create/write to an Excel 2007/2010 spreadsheet from an SSIS package
-
Comments posted to this topic are about the item Create/write to an Excel 2007/2010 spreadsheet from an SSIS package
-
What exactly is wrong with the Excel Destination in SSIS?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (2/11/2014)
What exactly is wrong with the Excel Destination in SSIS?Nothing. This article is demonstrating a proof of concept using a library without having to install Excel on a server. If all you want is a straight dump of data from SQL Server to Excel then the Excel Destination is fine.
If you want a more complex spreadsheet produced then the library will allow a much more tailored output. Apache POI allows something similar and there is a .NET port called NPOI on codeplex. http://npoi.codeplex.com/
Where it really gets interesting is that these libraries allow you to read Excel and all the bizarre formatting that gets put into a typical workbook.
I'm sure there will be an excellent follow up article on doing just that.
This is a good introduction starting with a nice simple example. The author hasn't fallen into the trap of producing an overly complicated example.
-
David.Poole (2/11/2014)
Koen Verbeeck (2/11/2014)
What exactly is wrong with the Excel Destination in SSIS?Nothing. This article is demonstrating a proof of concept using a library without having to install Excel on a server. If all you want is a straight dump of data from SQL Server to Excel then the Excel Destination is fine.
If you want a more complex spreadsheet produced then the library will allow a much more tailored output. Apache POI allows something similar and there is a .NET port called NPOI on codeplex. http://npoi.codeplex.com/
Where it really gets interesting is that these libraries allow you to read Excel and all the bizarre formatting that gets put into a typical workbook.
I'm sure there will be an excellent follow up article on doing just that.
This is a good introduction starting with a nice simple example. The author hasn't fallen into the trap of producing an overly complicated example.
The reason I reacted this way is because the article summary states the following:
but SSIS lacks direct support for writing Excel files
which just isn't true.
I get that SSIS can only do data dumps to Excel and that if you want nicely formatted Excel files you either use a library - like in the article - or you use an SSRS report. But SSIS does support writing to Excel.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Hi,
thanks for your interesting article! I would like to run your test scenario but I am stuck with following error:
...
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1' or one of its dependencies. The system cannot find the file specified.
File name: 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'
at ST_ff7fa3f713524285b0356dc135d66b25.csproj.ScriptMain.Main()
...
Does anyone have a hint for me?
many thanks, Thomas
-
t.mozelt (2/11/2014)
Hi,thanks for your interesting article! I would like to run your test scenario but I am stuck with following error:
...
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1' or one of its dependencies. The system cannot find the file specified.
File name: 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'
at ST_ff7fa3f713524285b0356dc135d66b25.csproj.ScriptMain.Main()
...
Does anyone have a hint for me?
many thanks, Thomas
Did you download the EPPlus files and set the reference correctly?
the package might not retain the DLL files internally so you will need to make sure that the DLLs are available on all target servers
-
yes, I followed the article exactly. The files are saved to a local directory and the correct reference set accordingly.
-
Is EPPlus showing up under references as shown in this screen shot?
-
yes, it is shown. i also tried to set specific version true/false. Same error. It builds fine but if I run the package the error shows up...
sorry
by the way- I am using Visual Studio2008 with .Net 3.51 Sp1 on a Win7 64bit machine
-
This article has some troubleshooting ideas for this error:
-
What exactly is wrong with the Excel Destination in SSIS?
Although SSIS does right fairly well to excel files, I find I have to employ inelegant "tricks" when it comes to formatting. If this library can help me avoid this (and thus avoid setting up tabs that have to be ignored by the users) I'm all for trying it. These kinds of libraries are meant to help take things further than would otherwise be possible.
-
Very interesting and helpful article. I love the open source and free availability to the DLL you provided. That said, we do a lot of advanced Excel manipulation at our location and have nothing but good things to say about SoftArtisans OfficeWriter ExcelWriter product (http://www.officewriter.com). Yes, it is expensive, but is VERY well supported and can do just about anything you want to do without having Excel actually installed. We do a TON with this product and have used it since it's ActiveX version. No, I don't sell it or get kick-backs. 😀
Haven't used WordWriter or any of their other products due to lack of need, but if you are in a nasty bind, this is a solid solution.
-
It was an interesting article but it is an inordinate amount of work to transfer data to excel (32bit?). What would you do to export your data if the number of rows were greater than the excel 32 bit limitation? A good sample of test data would be exporting the Sales.Orderdetail table from AdventureWorks2012 (121,000+ rows) to excel 64 bit excel.
I am having issues (see error below) trying to load from excel 64 bit as the source from excel 64bit 2013 and I am using VS 2012 ultimate & SSDT. Another issue is that VS2012 Ultimate & SSDT are a 32bit ide. Very tough to do things in 64bit mode.
[Connection manager "SourceConnectionExcel"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
My preference would be that SSIS should leverage either the 32 bit or 64 bit drivers transparently when creating a data flow.
-
I had to insert headers through ssis, found out you had to install excel on server, but I ended up with one script task for formatting/adding header. This seems like a whole lot more work.
-
Dear Creator of this wonderful EPPlus library,
Sorry for the down and dirty trench view ...
Does this mean we can get rid of the G_d D_mned AccessDatabaseEngine.exe install on the Server (Win/SQL 2008 R2) and not have to mess around with all of that run 32 bit mode and install 32 bit ver so we can see the code in BIDs 2008?
If no, please provide the GPS coordinates where you sleep most nights? (lol)
Honestly, even if we still have to deal with this AccessDatabaseEngine.exe garbage I still think your EPPlus stuff is wonderful and will definitely use it.
Thank you for creating it.
Regards,
Rich Torrone
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply