Friday, August 03, 2012 12:01 PM
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 :)
- Changed Type Santosh.pandyala Thursday, August 09, 2012 7:03 AM
Friday, August 03, 2012 12:05 PMModerator
easiest is to create an empty template and use that to replace (File System Task) the destination file before the data flow task starts
- Edited by SSISJoostMicrosoft Community Contributor, Moderator Friday, August 03, 2012 12:06 PM
- Proposed As Answer by DotNetMonster Friday, August 03, 2012 1:16 PM
- Unproposed As Answer by Santosh.pandyala Thursday, August 09, 2012 7:02 AM
- Proposed As Answer by Karthik Elangovan Friday, August 24, 2012 5:55 PM
Friday, August 03, 2012 12:44 PMFollow 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.
Tuesday, August 07, 2012 4:35 AM
i gone through the above discussion but i was not able to understand that one can u please explain me .
Sunday, August 12, 2012 4:02 AMModerator
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:
You can also refer to Rafael Salas's reply in the simlar thread:
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, August 23, 2012 2:29 PM
Monday, August 13, 2012 10:42 AM
This is the link which can be helpful for you
Monday, August 13, 2012 1:29 PM
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.
- Proposed As Answer by Ramasubramanian. S (Partner) Monday, August 13, 2012 2:31 PM
Tuesday, August 28, 2012 9:33 AM
Tuesday, August 28, 2012 2:09 PM
I am attaching the screenshot again,
Tuesday, August 28, 2012 3:12 PM
thanks for sharing screen shot but this one is not working ...
try once let me know is it working or not ??
Tuesday, August 28, 2012 5:21 PM
did you add your Data flow task after this Execute execute task, if so brief wha tare the actions you have taken.