Clean old data in excel sheet on every run in SSIS Package

1. Go to the SSIS package window and Drag the Data flow task à Double Click.
2. Drag the OLE DB Source and Excel Destination task in Data flow tab.

3. Double click on OLE DB Source and configure it with new connection.
I am using Adventure work database of SQL Server and table name : Department

4. Map the green arrow of OLE DB Source with Excel Destination.

5. Double click the excel task and configure it. Click New for configure Excel Connection Manager.
Specify the name of the file and its location and press ok as shown below.

6. Press the NAME OF EXCEL SHEET à New button. It will show the structure of mapping OLE DB connection as shown below. ( I rename sheet to TEST.)

7. Select the TEST sheet from drop down of NAME OF EXCEL SHEET. (Note: Select without $ name)

8. Press on Mappings at left side and map the column of Source to Destination. As shown below:

9. Press Ok, our package is ready for one time run but if we run second time then it will append the rows in the EXCEL.

10. To avoid the problem or Refresh the excel sheet with new data. We need to add EXECUTE SQL TASK in Control flow of SSIS package.

11. Open the EXECUTE SQL TASK with Double Click then configure it by selecting the EXCEL in connection and add script for sheet create and drop.

12. Enter the script as below ( Copy paste the create table from the EXCEL from step 6.)

13. Run the Package, it will replace the existing data in Excel on again run.