There is nothing inherently wrong with the Excel destination, but over the last several years I have found some significant limitations.

1) SSIS can only recognize and use the 32-bit MS Office drivers. Microsoft only allows either 32-bit or 64-bit drivers on a system - they cannot peacefully coexist.

2) To export to Excel you need to have a 32-bit copy of MS Office installed on the system. On a desktop this is not an issue, but on a server, it costs an additional license that will only be used by SSIS.

3) Mechanically speaking, the best way to use the Excel destination is to create a spreadsheet with all the named ranges you need and then copy it off to a template that you can copy as the correctly named spreadsheet file prior to export.

Personally, I don't want to install MS Office on my database servers just to use data tools that Microsoft should have provided the functionality for in the first place. I don't even want to go down the 32/64 bit path as it gets me mad enough to want to go out to Redmond with my butt kicking boots on.

Thanks for the article, it doesn't get me what I really want, but it does get me a way to what I need to do.