SSIS: Truncate Excel Destination

Question

I am creating a SSIS package that imporr data from a SQL Server Source to an Excel Destination.

How can one truncate spreadsheet before run? I tried the following way (using Execute SQL Task with no success.

Answer 1

Jet provider does not support neither truncate or delete command. You have 3 workarounds:

• Have an empty excel template that you clone before the running the dataflow, or
• Use execute sql task to create a new workbook/tab before running the dataflow
• Drop the worksheet using Drop Table TableCall_Log and create a new one. You can referer to this Link for more details.

Useful Links

• Deleting Records in an Excel Sheet using SSIS
• Import Header-Line tables into Dynamic Excel destinations II
• SSIS: Dynamically Generate Excel Table/Sheet

Answer 2

Truncation is not supported. You can recreate the whole excel file using two tasks:

1. The first task will be File task which deletes destination xls file.
2. The second task will be Execute SQL Task, which creates "table" (excel sheet). Use EXCEL connection type with excel connection manager and CREATE TABLE statement.

If you do not know the exact form of CREATE TABLE statement, then try to first prepare excel destination in a data flow task and by creating new excel sheet (by pushing New button on Connection Manager tab in Excel Destination editor) SSIS designer will show you CREATE TABLE statement which you need.

Connect the first task to the second task using Completion constraint if you are not sure, that the excel file exists every time you run the package.

You may also need to set DelayedValidation property to True on tasks following these first two tasks.