by ·52 Comments

This was a problem I racked my brains over for a while when I first started using SSIS. I found a few possible including using a File System task to copy a “Template” Excel file over the existing on, or generating unique files by appending the date.  I simply wanted to overwrite the existing data just like the Flat File Destination gives you that option.

My workaround was as flows:

Create the Data flow the way you normally would, then go to the Control View Then add two Execute SQL Tasks before your data flow is called so it looks like this

Step 1: Add Execute SQL Task 1 to Control Flow
Step 1: Add Execute SQL Task 1 to Control Flow

Set both Execute SQL Tasks to use the Excel file

Step 2: Set Connection Manager
Step 2: Set Connection Manager

Set your first Execute SQL Task’s SQL Command to drop your table which is also known as the sheet name of your Excel file.

Step 3: Drop Table
Step 3: Drop Table

Repeat step 3 on the second Execute SQL Command this time create your tables.

If you are new to SSIS or looking for some more advanced help, one of my favorite books is Professional Microsoft SQL Server 2012 Integration Services you should check it out.