Overwriting excel file data in SSIS

Question

I want to overwrite all data in an excel sheet using SSIS. I followed this guide https://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/. When I run my package I get the following error:

Description: Executing the query "CREATE TABLE Excel Destination ( clientNr ..." failed with the following error: "Table 'Excel Destination' already exists."

The problem lies with the "Drop Excel Table" Execute SQL Task. The "DROP TABLE Excel Destination" query does not delete the Excel Destination sheet in my case - it just deletes the headers, therefore a new sheet with the exact same name cannot be created. How can I delete the sheet instead of deleting the headers?

Renats Stozkovs · Accepted Answer · 2017-03-13 17:23:39Z

2

You would have to do that through C# / VB.NET script task. SQL task will not let you do what you wanted to do. Here is how you can do that, but basically you will need to get a worksheet by index (hopefully you have that), change the name and then save it. Pretty straight forward in Script task:

object m = System.Reflection.Missing.Value;
Excel.ApplicationClass app = new Excel.ApplicationClass();
Excel.Workbook xls = (Workbook)app.Workbooks.Open(path, m,m,m,m,m,m,m,m,m,m,m,m,m,m);
Excel.Worksheet sheet = (Worksheet)xls.Worksheets.get_Item(1);
sheet.Name = "myName";
xls.Save();
app.Application.Workbooks.Close();

answered Mar 13, 2017 at 17:23

Renats Stozkovs

2,63510 gold badges24 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DanteeChaos Over a year ago

In the end I decided to create a new excel file each time the SSIS package is initiated and then delete it afterwards. It acomplishes the same result without the need for scripting. Thanks for the contribution - I'm sure your script will help someone in the future.

Collectives™ on Stack Overflow

Overwriting excel file data in SSIS

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related