If there is one process that should be simpler than it is out of the box, it is creating Excel spreadsheets from SSIS. Over the years i’ve tried doing it a number of ways, using the built in component, Interop , OLE DB etc all suck to one degree or another. Either unreliable or to slow or simply unusable.
A twitter conversation, A) proved I wasn’t alone and B) Pointed me in the direction of EPPlus.
Over on SSC there is already a post on using EPplus with SSIS, some of which, such as putting EPPlus into the GAC, is still relevant for this post.
However, right now, i have a big love of BIML, simply put i think that this is what SSIS should have been in the first place and I personally find all the pointing and clicking a real time sink. Additionally, in BIML, one you have written a package to do something , ie a simple dataflow, its a snip to repeat that over 10, 20, 50 , 100 or 1000s of tables. But the real time saver for me is when you need to re-architect, ie turn sequential dataflows into a parallel dataflow. Its only really a case of changing where you iterate in your BIML code.
Anyway, i’ve combined these two pain points to create a BIML routine that uses EPPlus to output multi-sheeted Excel spreadsheet reliably and fast.
At the moment its very basic , take SQL statements and output the data to an excel file, but in time i will be hoping to create some meta data to start ‘getting the crayons out’ and making them look a bit more pretty.
Code is on GitHub at https://github.com/davebally/BIML-SSIS-Excel-Output , hope this of use to someone.