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
Beautiful article. Totally clear from beginning to end. Can’t thank you enough.
Good Article.. Helped me in one of my issues… Thankx
This works well…though with a lot of tweaking. Why does BIDS allow DROP TABLE `Excel Destination` but not DROP TABLE “Excel Destination” still beats me.
Thank-you, the information was very helpful to get my SSIS package working.
very nice article…thanks so much..
good article…thanks