SSIS Script Task Delete Row from excel Sheet

Question

I have an SSIS package that creates and populates an excel sheet, it creates the sheet from a template file.

One of the issue's I had was that excel would change the format of the rows. I did a work around of inserting a header row and hiding it.

However I now need to script a VB task in SSIS that opens the excel sheet and deletes that specific row.

I've seen some articles online however have not been able to find any code I can try and replicate and my VB knowledge is very limited and I am really struggling. SO far i've figured out how to delete the row.

Sub DeleteRow1()
Rows(4).Delete
End Sub

However i need to assign file strings and get the file to open and close as well...

GPH · Accepted Answer · 2015-03-03 16:02:28Z

3

after some discussions with a programmer here i managed to get some script to do exactly what I needed. Posting as an answer for anyone else out there who ever needs to do this in SSIS.

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet

xlApp = New Excel.Application
xlApp.Workbooks.Open(Dts.Variables("NewFileName").Value.ToString)
xlSheet = xlApp.Workbooks(1).ActiveSheet


xlSheet.Rows(4).Delete()

xlApp.Workbooks(1).Save()
xlApp.Workbooks(1).Close()
xlSheet = Nothing

'
Dts.TaskResult = ScriptResults.Success

Uses a variable in SSIS to get the file name. This works perfectly although you do need to add Microsoft.Office.Interop.Excel Reference.

answered Mar 3, 2015 at 16:02

GPH

1,9073 gold badges29 silver badges50 bronze badges

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

Comments

Tom K. · Accepted Answer · 2015-03-03 11:57:27Z

1

Sub deleteRow1()

Dim wkbk As Workbook

'You can also use an input box like this
' Dim wkbkname As Variant
' wkbkname = InputBox("Enter Workbook Name here.")
' Use wkbkname instead of wkbk then

Set wkbk = "Filename"

wkbk.Sheets(1).Rows(4).delete

End Sub

I hope that's what you were looking for.

answered Mar 3, 2015 at 11:57

Tom K.

1,0421 gold badge12 silver badges28 bronze badges

Collectives™ on Stack Overflow

SSIS Script Task Delete Row from excel Sheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related