CODEWITHSUNDEEP
sqlexcelssis
DanteeChaos
DanteeChaos

Reputation: 333

Overwriting excel file data in SSIS

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?

Upvotes: 0

Views: 1672

Answers (1)

Renats Stozkovs
Renats Stozkovs

Reputation: 2605

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();

Upvotes: 2

Related Questions