SSIS: Dynamically Generate Excel Table/Sheet

Sol:If your target is Excel and you
want to refresh/load Excel with new set of data without creating a new file and
you want your destination sheet name fixed too, yes it’s possible!
How? Create a new excel table
(Sheet) instead of creating a new file each time.
Open BIDS
Create a new package, go to
Control Flow tab
Drag two “Execute SQL Task”
task and one “Data Flow Task”
Give name “Drop Excel Table”
and “Create Excel Table” to two “Execute SQL Task” above
Open/Double click “Data Flow
Task”
Drag an “OLE DB Source” Source
Note: Since already have AdventureWorksDW sample database, I will
use this for my example
Create a connection
AdventureWorksDW to database table DimPromotion or use below SQL
SELECT [PromotionKey]
     ,[PromotionAlternateKey]
     ,[EnglishPromotionName]
     ,[SpanishPromotionName]
     ,[FrenchPromotionName]
     ,[DiscountPct]
     ,[EnglishPromotionType]
     ,[SpanishPromotionType]
     ,[FrenchPromotionType]
     ,[EnglishPromotionCategory]
     ,[SpanishPromotionCategory]
     ,[FrenchPromotionCategory]
     ,[StartDate]
     ,[EndDate]
     ,[MinQty]
     ,[MaxQty]
 FROM
[AdventureWorksDW].[dbo].[DimPromotion]

Create a new connection to a Excel file in C:\DimPromotion.xls
Now drag “Excel Destination”
and configure this to use connection created above and then create a new excel table as shown below