Nice offering MM. The technique is one I have seen outlined before but not necessarily applied to SSIS. While no one step is difficult there are quite a few of them. What you have are the makings of an outline for a nice article on working with Excel in SSIS ;-)
Thanks for the nice words.
Maybe it seems like a lot of steps, but it really is easy - just create the template, save it, unzip it and store it somewhere accessible.
Then SSIS generates the Sheet.xml - easy enough - and zips it all up - easy enough.
There are really only two steps to the SSIS part, unless you want to unzip it every time, but I can't see the point in that.
It only gets more complicated if you also want to use the Shared Strings feature, but that is just another XML file to create in SSIS - one more step.
As for an article...can I squeeze that in sometime in 2020 ... maybe?
I'm wondering whether that <sheet> XML could be produced in pure T-SQL. If someone took the time to work this out, it would be a real service to the community. If people pledge enough beer money, I might even be tempted to investigate ... :-D
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.