Hopefully here are some answers to the questions:

1. The filename actually is a variable. The variable was populated in the first Execute SQL task BEFORE the Foreach Loop. (Like a cursor). You dump values from a table into a variable.

Set the Excel Connection (notice that usually you specific connection 1, connection 2 etc but here NO specified number) to use a New Property expression. In the New Property expression and add the variable defined for the Excelsheet name. This variable references back to the cursor (called in this case 'Region' from the Execute SQL task).

As to the tabs...it is the same thing. A cursor is populated in the Execute SQL task called 'subregion'. This populated cursor is then used in a variable statement in the Foreach loop allowing the tabs to be created. Use the 'User:ListofSubgroups' value in the Variable name field within the Excel Destination Editor to create the tabs on the fly.

Now...as a 'heads up' To be able to create the Excel Destination the first time when developing the solution an actual Excel workbook with at least one tab must exist.

2. As with any solution there are definitely more ways than this one to 'skin a cat' and do this operation. This solution does not discuss how to push to 2007 Excel (we have just figured that one out). AND there is actually a way to totally code creation of an excel spreadsheet and tabs and population of same. It is not mine so I will not post here. This solution is for those that use SSIS packages preferentially.

The original motivator was to be able to do this WITHOUT having a template sitting on some filesystem. To that end we are one step closer to keeping everything in a SSIS solution.

Hoping the above helps....please keep the questions coming