SSIS: Dynamically Generate Excel Table/Sheet

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

Copy above generated create script to notepad as we need this later!

Next step is to configure
“Create Excel Table”, choose ConnectionType as EXCEL and point this to
connection create above, use below for SQLStatement

CREATE TABLE
`Excel Destination` (

    `PromotionKey` INTEGER,

    `PromotionAlternateKey` INTEGER,

    `EnglishPromotionName` NVARCHAR(255),

    `SpanishPromotionName` NVARCHAR(255),

    `FrenchPromotionName` NVARCHAR(255),

    `DiscountPct` DOUBLE
PRECISION,

    `EnglishPromotionType` NVARCHAR(50),

    `SpanishPromotionType` NVARCHAR(50),

    `FrenchPromotionType` NVARCHAR(50),

    `EnglishPromotionCategory` NVARCHAR(50),

    `SpanishPromotionCategory` NVARCHAR(50),

    `FrenchPromotionCategory` NVARCHAR(50),

    `StartDate` DATETIME,

    `EndDate` DATETIME,

    `MinQty` INTEGER,

    `MaxQty` INTEGER

)

Now configure ““Drop Excel
Table”, choose ConnectionType as EXCEL and point this to connection create
above, use below for SQLStatement

DROP TABLE `Excel Destination`

Finally package looks like
below