How to Overwrite an Excel sheet using SSIS or How to Delete data Before Inserting into Excel Sheet

Question

Hi all,

i facing a problem with Excel Sheet, when i running SSIS package at that time it appending the data to already existing data .but i want to overwrite the data or delete the data and reinsert the data into excel sheet.

and i tried Drop table Excelsheet; and agian using another execute sql task creating Excelsheet but it not working :( .

so please share your answer here .

thank u all :) 

Answer 1

easiest is to create an empty template and use that to replace (File System Task) the destination file before the data flow task starts

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

Follow this which discusses a similar problem. In a nnutshell SSIS cannot simply overwrite the excel but the same can be achieved via a bit of manipulation. As SSISJoost mentioned use an empty template to replace the destination before you start pumping in the new data.

---

http://btsbee.wordpress.com/

Answer 3

hi btsbee,

i gone through the above discussion  but i was not able to understand that one can u please explain me .

thanks . 

Answer 4

Hi Santosh.pandyala,

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, please see detail steps in the following link:
http://samuelhaddad.com/2009/03/31/overwriting-an-excel-file-destination-using-ssis/

You can also refer to Rafael Salas's reply in the simlar thread:
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/9c21b7ee-5737-469d-8b96-62e0cad41616

Thanks,
Eileen

Answer 5

This is the link which can be helpful for you

http://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/

Answer 6

Hi Santhosh,

You can used the below mentioned Steps to perform the deletion in Excel:

1. Add and Execute SQL task to your Package, in that task configure the connection properties as mentioned below:

Note: You need to add your excel files connection manager name to your Execute SQL task Connection type

Attached the screenshot for your reference.

---

Ramasubramanian S

Answer 7

hi Ramasubramanian

i was unable to see the screen shot ,can u share the screen to my mail id [e-mail]

Answer 8

Hi,

I am attaching the screenshot again,

---

Ramasubramanian S

Answer 9

hi ,

thanks for sharing screen shot but this one is not working ...

try once let me know is it working or not ??

Answer 10

HI,

did you add your Data flow task after this Execute execute task, if so brief wha tare the actions you have taken.

---

Ramasubramanian S