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.
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT
on a production system! After all, you
will be the one supporting it!
Links: For better assistance in answering your questions
, How to ask a question
, Performance Problems
, Common date/time routines
,CROSS-TABS and PIVOT tables Part 1
& Part 2
, Using APPLY Part 1
& Part 2
, Splitting Delimited Strings