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!

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 2

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)

Answer 3

Yes, Possible!

Using SSIS we can solve this problem:

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

Answer 4

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 5

Using a CSV file with flat-file connection manager would serve your purpose of overwriting.