How to overwrite Excel destination in SSIS?

Question

I have created a package to fetch data from two SQL Server tables, and using merge join combined this data, then stored the result into an Excel destination.

The first time it works fine. The second time it stores repeated data in the Excel file.

How do I overwrite the Excel file rows?

Answer 1

Yes, Possible!

Using SSIS we can solve this problem :

first of all, crate a Excel format file(Structure Format using Excel Connection Manager) at one location and create a copy of that excel file using FILE SYSTEM TASK another location and make sure that SET Is-Overwrite=True , and using data flow task insert data into new file copied file. whenever we want insert data it will create blank excel file and then load the data

Answer 2

For Excel it will append data. There is no such option available for overwriting data.

You have to delete and recreate the file through the File System task.

Answer 3

Yes, Possible!

Here is the solution:

First go to your Excel Destination Click to New Button next to Name of Excel Sheet, copy the DML query inside. Then put an Execute SQL Task into your Control Flow and connect it to your data flow that contains Excel destination. Set the Connection Type To Excel, Set the Connection to your Excel Destination's Excel Connection Manager, go to SQL Statement and type :

Drop TABLE `put the name of the sheet in the excel query you just copied`
Go

finally paste the query after it.

It is all you need to do to solve the problem.

You can refer to this link for a complete info: http://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/

Answer 4

Unfortunately the Excel connection manager does not have a setting that allows overwriting the data. You'll need to set up some file manipulation using the File System Task in the Control Flow.

There are several possibilities, here's one of them. You can create a template file (which just contains the sheet with the header) and prior to the Data Flow Transformation a File System Task copies it over the previously exported file.

The File System Task (MSDN)