-2

I have a SSIS package in which Data is loaded from Sql server to Excel sheet. Now, whenever I run the package data got loaded with the previous loaded data. Requirement is to load only the fresh data and remove the existing data from excel sheet. Can we use Execute sql task to Truncate data in the excel sheet also ?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Vishal Mishr
  • 195
  • 1
  • 8
  • 3
    Possible duplicate of [SSIS: Truncate Excel Destination](https://stackoverflow.com/questions/43452541/ssis-truncate-excel-destination) – Hadi Apr 14 '19 at 13:51

1 Answers1

0

An Execute SQL TASK can be used to drop and then recreate the excel sheet so that only new data will be left following the import. To do this add an Execute SQL Task to the Control Flow and choose EXCEL for the ConnectionType property. For the SQL Statement, enter a DROP command with the table name followed by a CREATE command containing the DDL of the table (excel sheet). An example of this is below. Note that the GO keyword is necessary to run both the CREATE and DROP statements in the same task, otherwise a separate Execute SQL Task will be needed for each one.

Example Excel DROP and CREATE Commands:

DROP TABLE SheetName

GO

CREATE TABLE SheetName (
    `ID` Long,
    `NAME` LongText,
    `ImportDate` DateTime
)
userfl89
  • 4,610
  • 1
  • 9
  • 17