Why Is Creating Excel Sheets From Ssis So Hard

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.