Utilize an Execute SQL Task (connected to an Excel Connection), and run a "CREATE TABLE " statement. If the xls file doesn't already exist, it will be created. It will also create a worksheet AND a named range of the name specified in the CREATE TABLE statement (the difference is the worksheet has a $ appended to the end).

Repeat for each worksheet that you want to create.

For each worksheet, use a Data Flow task, with a Source and Destination. If your source has char/varchar columns, you will need to use a derived column task to make nvarchar columns. (Alternatively, for your source use a query and convert(nvarchar()) all the char/varchar columns).

Note that the create table statement needs to use the "`" instead of the ' character, and the data types need to be what excel supports.

The easiest way to set up your initial package is to utilize the Export Wizard, and export your source to an Excel file. Save it as a package, and you'll have a good baseline to work with for all the remaining worksheets.

Hope this helps. Come on back if you have any more questions.