This post may raise more questions for you than it answers, but it really is something that happenned in my shop and confused some people.  So I'm creating this post as a place holder so I'll be able to reference this 10 months from now when the same issue comes up and I've forgetten the solution.

Basically for whatever reason we use Excel, darn !  No matter how I try and recreate these Excel sheets in Reporting Services, some business users won't bite and they still need excel files.  Generally these SSIS to Excel data pumps work fine....the ones that generally trip the process up are ones with long text and multiple "named" work sheets.  To solve the "named" work sheets problem we often use an Excel template file that is copied to the destination and renamed, than the data pump occurs.  As we migrate the SSIS packages from Development, QA to production, sometimes an "odd" excel worksheet gets introduced, usually named "sheet1", lovely, below is the procedure we used to correct this...

Thanks to Tom Reeves from our team.

To Delete Unwanted sheets in Excel for use in SSIS

1) Map a local drive to the E: drive of the app server (ServerNameHere\E$)
2) Open template file on server and delete unwanted tabs
3) Open SSIS package
a. You’ll need to make note of the tab name(s) that you are working with
b. Copy query from the source object in the data flow
4) Create a new DTS package – SQL2000
a. 1 SQL Connection
b. 1 Excel Connection
c. 2 Execute SQL Tasks
i. Both pointed to the Excel connection manager
ii. One for Drop table and one for Create table
5) Open the Transformation flow between the SQL connection and the Excel connection - DTS
6) Copy and paste the query from the SSIS package into the Source tab
7) Click the Create button on the Destination tab and copy the code from the window.  You can hit cancel on this screen now.
8) Open one of the Execute SQL Tasks and set the connection manager to the Excel connection and type your Drop sql statement. 
a. Example: drop table `SheetNameHere` - No dollar sign in the table name
8) Open the other Execute SQL Task and set the connection manager to the Excel connection and type your Create sql statement.
Example:
CREATE TABLE `SheetNameHere` (`Division_Code` Decimal (2,0) ,
`Division_Dept_Id` Decimal (7,0) ,
`Office_Dept_Id` Decimal (7,0) ,
`Last_Update_Date` DateTime ,
`Reason_Text_Opt_out` LongText ,
`Reason_Text_Opt_in` LongText ,
`WHQ_Comments` LongText ,
`WHQ_User` VarChar (30) ,
`WHQ_Last_Update_Date` DateTime ,
`military` Short ,
`office_type_code` VarChar (1) ,
`ABC` VarChar (10) ,
`Client_Segmentation` VarChar (50) ,
`number_of_windows` Long ,
`number_of_desks` Long ,
`Number_of_bilingual_tax_pros` VarChar (50) ,
`Year_round_indicator` VarChar (1) ,
`Latino_designation_year_prior` Long ,
`office_status` VarChar (1) ,
`Notary` Long ,
`ITIN` Long ,
`Spanish_Speaking_Preparer` Long  )