none
Excel 2007 Connection -- DROP TABLE command

    Question

  • Can we execute a DROP TABLE tableName command for an Excel 2007 sheet?  I have tried using the Execute SQL Task with an Excel connection and an OLEDB connection and I keep returning to the error that table name does not exist, which is better than when it runs, goes green but deleting nothing.

    My CREATE TABLE statement works fine, unless of course the sheet already exists which is why I am trying to use a separate task, same connection manager to delete the sheet. 

    [Execute SQL Task] Error: Executing the query "DROP TABLE [hello]" failed with the following error: "Table 'hello' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Sheet Name is hello
    ResultSet property is none -- have tried all possibilities

    Thursday, May 06, 2010 8:02 PM

All replies

  • Hi Sandra,

    Please use this syntax: DROP TABLE [NameOfExcelSheet$]


    BI and ERP Senior Consultant @ Nexum Bogazici | If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    SSIS Nedir?

    • Proposed as answer by RedCritic Thursday, May 06, 2010 8:50 PM
    • Unproposed as answer by Sandra Mueller Friday, June 25, 2010 8:38 PM
    Thursday, May 06, 2010 8:21 PM
  • I have tried that and it only drops the named range in 2007 not the sheet.  Actually -- it only deletes the contents of the named range, not the range itself.
    Friday, May 07, 2010 1:03 PM
  • My experiance with the scenario that you have was

    i had to make/create tables (sheets) in a excel file and etc..... so what i did i made a empty excel file and just Created the tables , each time the package was running i was using mu template empty excel file.

    i am sure they are ways to delete the Sheet through SSIS.

     


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, May 07, 2010 4:28 PM
  • SH -- I would think so but I haven't found it.  Seen a LOT of posts and comments about the Excel 2007 driver not supporting this.  I have tried using constraints to make if it doesn't exist and then just delete...  Went down a trail of autogenerating sheet names using variables and timestamps.  It all works but we finally just settled on if they did not delete the upload comments the flow appends to the end of what was there.  Unfortunately that does leave me a failure if they leave the sheet but delete the column headers. 

    I would love to find a way to delete the sheet.

    Friday, May 07, 2010 4:45 PM
  • Well Sandra, than you may have to return back to scripting and excel object model. Just open excel in script task and delete the sheet inside. I am googling to find a sample code. i will post it when i test it.
    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Friday, May 07, 2010 4:59 PM
  • One of the experts that i think is the best on Excel and SSIS is

    http://dougbert.com/

    i am looking to

     


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, May 07, 2010 5:09 PM
  • assuming that you can not use DELETE Sheet , what scenarios will help you

    please tellm e what will help, i need examples,


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, May 07, 2010 5:11 PM
  • Hi Sandra,

    If you like you can use the code. (Use it by your own risk).

    In order to send the sheet name as an input parameter, you must assign it to a variable. In my example code below, the variable is package scope string variable named: "ExcelSheetName". Change the excel source in Dim ExcelFilePath ="........" (If you like you can do the Excel FilePath as a variable.)

    Additionally, add a reference to script task. In VSTA Script Editor, Menu->Project->Add Reference. On Add Reference window, browse to tab "COM" and select "Microsoft Excel 12.0 Object Library" and click ok.

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic 2008.
    ' The ScriptMain is the entry point class of the script.
    
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports Microsoft.Office.Interop
    
    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    
     Enum ScriptResults
      Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
     End Enum
    
    
     Public Sub Main()
    
      Dim oExcel As New Excel.Application
      Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
      System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
      Dim oBooks As Object = Nothing
      Dim oSheet1 As Object = Nothing
    
      Dim SheetName As String = Dts.Variables("User::ExcelSheetName").Value '"Hello"
      Dim ExcelFilePath As String = "D:\oozturk\Desktop\Book22.xlsx"
    
      oExcel.Visible = False
      oExcel.UserControl = False
      oExcel.DisplayAlerts = False
      oExcel.ScreenUpdating = False
    
      Try
    
       'Open the workbook (assumes oExcel is the application object).
       oBooks = oExcel.Workbooks.Open(ExcelFilePath)
    
       'Get a reference to the first worksheet.
       oSheet1 = oBooks.Sheets(SheetName)
       oSheet1.Delete()
    
       oExcel.ActiveWorkbook.Save()
       oExcel.ActiveWorkbook.Close()
    
       Dts.TaskResult = ScriptResults.Success
    
      Catch ex As Exception
       Dts.Events.FireError(9999, "Error Excel Sheet Deletion", ex.Message, "", 1)
       Dts.TaskResult = ScriptResults.Failure
      Finally
       'Restore Culture Info. 
       System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
    
       'Close Down Excel. Must Remove all references to COM objects that are created, 
       'or Excel will remain as running process 
       Call ReleaseCom(oSheet1)
       Call ReleaseCom(oBooks)
       oExcel.Quit()
       Call ReleaseCom(oExcel)
       GC.Collect() : GC.WaitForPendingFinalizers()
      End Try
    
     End Sub
    
     Sub ReleaseCom(ByVal o As Object)
      Try
       If Not o Is Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
      Catch ex As Exception
       Dts.Events.FireError(9999, "Error Excel Sheet Deletion", ex.Message, "", 1)
      Finally
       If Not o Is Nothing Then o = Nothing
      End Try
     End Sub
    
    End Class

    Hope that helps.

    (Dont forget! Use it by your own risk and this code needs to be Excel installed on runtime environment, and it could be a problem about virtual memory control. (Thanks Nik3 - Shahriar Nikkhah for warning.)

    Onur


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Friday, May 07, 2010 6:10 PM
  • Hi Sandra,

    A simple work-around I have found is doing the create table on the excel sheet: (ExcelReport.XLSX).  Before doing any further processing, manually copy your XLSX and rename it.  (ExcelReportTemplate.XLSX).  Prior to doing the Export to XLSX (running the package), use a File System task to copy the template (with Overwrite destination) to the orginal report location (Copy Source - ExcelReportTemplate.XLSX/ Copy Destination - ExcelReport.XLSX). 

    Hope that helps.

    JS

    Friday, May 07, 2010 6:17 PM
  • HI Onur Omer Ozturk

    few questions , i liked the script i didnt test it but ,

    will this script on a production server BOX ?

    Do we have to have Office installed on the production BOX? i am assuming the answer is NO , becase if the answer is Yes, you know what will happend to virtual memory control, while having Office installed on a Server Box?

    i am assuming that the code only deletes Excel sheets, right ?

    thanks you very very very much for the code.


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, May 07, 2010 6:31 PM
  • Hi Shahriar Nikkhah,

    Thanks for the correction, for sure, i forgot to mention it. This code needs excel is installed on Server. And it deletes excel only the sheet.

    Not the best solution, i know it :)

    It is just a trade off between deleting the sheet and recreating it or just changing the business model. Now i also agree on drilling deep into business model since Excel Object model doesnt allo us to delete it. Now we question do we really delete it?

    -Sandra what do you say about it?? Could you please tell us more abot the need that you ended up deleting the sheet?

    Regards

    Onur

     

     


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Friday, May 07, 2010 6:49 PM
  • Hi  Onur

    i assumed so that excel needs to be installed and i don't think that the owner of the BOX (cutomer) will allow you to do that becasue long story short having office installed on a production OS server will suck out all of the memory and even take over lots of thing that is not needed.

    but anyways, its a very very good code , thank you again

    and i did ask Sandra that what .....

    assuming that you can not use DELETE Sheet , what scenarios will help you please tell me what will help, i need examples,

    ......

    by knowing the examples we may have a way to by-passing this deleting problem

    if you want to delete a sheet you need to have access to the Excel OBJECTS like "Dim oExcel As New Excel.Application" but we dont want to do that

    but for crating a table (sheet) in excel you dont need access to Excel Objects , if you get my point.

    anywasy thanks again.

     


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, May 07, 2010 6:59 PM
  • sorry -- SSIS has been down on my servers and working that rather than developing...

    assuming that you can not use DELETE Sheet , what scenarios will help you -- Nothing.  I would stick with what I have, appending data and using a date column

    As for the Excel VBA Code -- VB.NET Code -- I will try that.  Get so hung up on SQL that sometimes I forget about the code portion.  Will advise on success or failure...

    Thanks all for the feedback and advice

    Friday, May 07, 2010 7:19 PM
  • As for the Excel VBA Code -- VB.NET Code -- I will try that.

    first ask your customer will they allow you to install office on production Box or not. good luck.
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, May 07, 2010 7:33 PM
  • As for the Excel VBA Code -- VB.NET Code -- I will try that.

    first ask your customer will they allow you to install office on production Box or not. good luck.
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)


    Shahriar is right. Ask it please and dont forget the warnings ! 

    Btw, please give us details of why you need to delete a sheet? and why you need to create one again?

    Thanks


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Friday, May 07, 2010 7:40 PM
  • If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:

    • Excel Source component - for reading data from Excel worksheet.
    • Excel Destination component - for writing data in Excel worksheet.
    • Excel Task - for manipulating Excel workbooks.
    • Excel Connection - used by the components above and also for implementing custom scripts based on it.
    The Excel Task supports dropping of Excel worksheet and it doesn't require installation of Office components. Cheers!
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Wednesday, June 09, 2010 11:01 PM
  • Cozy -- there is no way where my current contract is that I can get them to buy the add on in time for me to use it.  Too many Oracle systems and only one SQL.  I have looked at the tools though and have already started talking my next client into them -- especially a couple of the ones in beta.
    Friday, June 25, 2010 8:40 PM
  • why dont you have a excel template and add the data to a NEW sheet that you will be making throught the ssis insted of deleting the sheet and maybe remaking it?

    why can you have a template ?

     


    Sincerely SH --MCTS 2005 & 2008 MCITP 2008 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s)
    Friday, June 25, 2010 8:51 PM
  • I am over time slot for this task.  Honestly it was just way simplier to add a date and have them scroll thru the list to the load for that date.  Besides, I am already having issues with running on 64 bit and I have been reading that script tasks cause problems?
    Friday, June 25, 2010 8:59 PM