Need to delete last row in Excel sheet using SSIS script task

  • Hi to all.

    I need to delete last row in excel in sheet using ssis task. can any help me regarding this?

    Please do let me know in case of any additional information required.

    Thanks in advance.

  • 1. Connect to the excel sheet using JET oledb.

    2. Query the whole sheet and create a datatable which contains the excel data now.

    3. Delete the first row of Datatable.

    4. Update the datatable using Dataadapter (This will update the change to excel)

    OR

    Use update command as below:

    update `Sheet Name`

    set [Column Name]=null where [Condition Column Name] = 'Sample'

  • Thanks jitendra. i will try your solution.

    But i am looking for Script Task.

    Thanks in advance.:-)

  • Hi to all.

    i didnt got any reply . am i missing any information ?

  • try using sql script task-

    Dim filename As String

    Dim appExcel As Object

    Dim newBook As Object

    Dim oSheet1 As Object

    Dim cell As String

    Dim rowcount As Int16

    appExcel = CreateObject("Excel.Application")

    filename = "C:\Documents and Settingsbkrrov\Desktop\test.xls"

    appExcel.DisplayAlerts = False

    newBook = appExcel.Workbooks.Open(filename)

    oSheet1 = newBook.worksheets("Sheet1")

    rowcount = oSheet1.UsedRange.Rows.Count()

    cell = "A" + rowcount.ToString

    oSheet1.Range(cell).Entirerow.Delete()

    With newBook

    .SaveAs(filename, FileFormat:=56)

    End With

    appExcel.Workbooks.Close()

    appExcel.Quit()

    Thanks

    Sneh

  • Thanks sneh.

    it is working fine....

  • I am trying to do something similar, but I cannot figure out how to close the excel instance from task manager. Any assistance? code snippet below

    Public Sub Main()

    '

    Dim objExcel As Object

    objExcel = CreateObject("Excel.Application")

    objExcel.Workbooks.Open("*.xls")

    objExcel.displayalerts = False

    objExcel.Sheets("*").Select()

    objExcel.rows("2:50000").delete()

    objExcel.Workbooks(1).Save()

    objExcel.Workbooks.Close(False)

    objExcel.displayalerts = True

    objExcel = Nothing

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply