Delete sheet from the excel

Question

Can any body guide me how i can delete sheet from the excel using ssis

Answer 1

Can't be done. Maybe you can do something inside the object model in Office but as far as native tasks available in the toolbox? no.

I have fojund that having a template Excel document in the background is good. Then you simply use File System tasks to copy and move to your new location.

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 2

Hi,

You can achieve this by using the interop.excel assembly in script task.

Something like

Sheets

 Dim appexcel As Object
        Dim newbook As Object
        Dim Sheet As Object
        Dim objFSO As Object
        
        Dim Connection As String = Dts.variables("User::V_FilePath").value 
        
        appexcel = CreateObject("Excel.Application")
        newbook = appexcel.Workbooks.Open(Connection)
        Sheet = newbook.worksheets("Sheet1")
ActiveWindow.SelectedSheets.Delete
appexcel.DisplayAlerts = False
            newbook.Save()
            appexcel.DisplayAlerts = True
            newbook.Close(False)
appExcel.quit()

The code would be somethng like above(Little bit of Syntax error because my coding is not good off the VS :)

Please mark it as helpful if it helps.

Thanks

Sumit

Answer 3

The only way is by using a Script Task (or may be by using Third Party components). Big downside is that you need office on your server which is a bad practice and not supported by Microsoft. Best way is by using a template (copy of your empty excel file) and copy it to the destination location before filling it with the Data Flow Task.

It is also possible to drop/create a table in excel, but that doesn't delete the actual sheet:
http://dwhanalytics.wordpress.com/2011/04/07/ssis-dynamically-generate-excel-tablesheet/

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter