I have never found a way to replace data in an existing Excel file. The standard behavior is to append data. The only workaround is to have excel.exe on the server and use Interop. Too bad

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Option Strict Off

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Private _app As Microsoft.Office.Interop.Excel.Application

Private _books As Microsoft.Office.Interop.Excel.Workbooks

Private _book As Microsoft.Office.Interop.Excel.Workbook

Protected _sheets As Microsoft.Office.Interop.Excel.Sheets

Protected _sheet As Microsoft.Office.Interop.Excel.Worksheet

Public Sub Main()

' Add your code here

Try

Dim FileName As String

Dim Tab As String

FileName = CType(Dts.Variables("DestinationFile").Value, String)

Tab = CType(Dts.Variables("AccessTB").Value, String)

OpenExcelWorkbook(FileName)

_sheet = CType(_sheets(Tab), Microsoft.Office.Interop.Excel.Worksheet)

_sheet.Select(Type.Missing)

Dim range As Microsoft.Office.Interop.Excel.Range = _sheet.Rows("2:65536")

range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp)

NAR(range)

NAR(_sheet)

CloseExcelWorkbook()

NAR(_book)

_app.Quit()

NAR(_app)

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

MsgBox(ex.ToString())

End Try

End Sub

Protected Sub OpenExcelWorkbook(ByVal fileName As String)

'try

'{

_app = New Microsoft.Office.Interop.Excel.Application()

If _book Is Nothing Then

_books = _app.Workbooks

_book = _books.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

_sheets = _book.Worksheets

End If

'}

'catch(Exception ex)

'{

' Console.WriteLine(ex.ToString());

'}

End Sub

Protected Sub CloseExcelWorkbook()

_book.Save()

_book.Close(False, Type.Missing, Type.Missing)

End Sub

Protected Sub NAR(ByVal o As Object)

Try

If Not o Is Nothing Then

System.Runtime.InteropServices.Marshal.ReleaseComObject(o)

End If

Finally

o = Nothing

End Try

End Sub

' To avoid the variable locking problem use this method to read package variables.

' No read variables need to be declared in the "ReadOnlyVariables" list of the script

' component if this method is used inside the code.

Private Function ReadVariable(ByVal varName As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockForRead(varName)

Dts.VariableDispenser.GetVariables(vars)

Try

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

' To avoid the variable locking problem use this method to set package variables.

' No write variables need to be declared in the "ReadWriteVariables" list of the script

' component if this method is used inside the code.

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)

Try

Dim vars As Variables

Dts.VariableDispenser.LockForWrite(varName)

Dts.VariableDispenser.GetVariables(vars)

Try

vars(varName).Value = varValue

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

End Sub

End Class