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