none
Deleting Records in an Excel Sheet using SSIS

    Question

  •  

    I'm using SSIS to export data from a SQL table to an Excel SpreadSheet. The first row of the Excel sheet contains the headers. I would like to delete all the records in the sheet before exporting the data. I'm using an Execute SQL task and the ConnectionType is set to Excel, the connection is set to the correct Excel Connection Manager, the ResultSet is set ton none  and the query is DELETE FROM [ExcelDeneme$] GO. When I run the package I receive the following error :[Execute SQL Task] Error: Executing the query "DELETE FROM [ExcelDeneme$] " failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    What can i do to solve this problem?


     

    Thursday, March 27, 2008 8:37 AM

Answers

  •  MatthewRoche wrote:

    I think this error probably means "Excel doesn't let you DELETE records like this."

     

    Wht not create a new spreadsheet instead of trying to delete all of the records?

     

    That is correct, Jet provider does not support neither truncate or delete. You have 2 options:

    • Have an empty excel template that you clone before the running the dataflow, or
    • Use execute sql task to create a new workbook/tab before running the dataflow
    Thursday, March 27, 2008 1:47 PM
  •  

    Place the sheet(table) name is a variable; then In the Excel Destination Component, Change the data access mode to ‘Table Name or View Name Variable’ and choose the variable name from the drop down list.

     

    http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html

     

    Friday, May 02, 2008 5:13 PM

All replies

  • I think this error probably means "Excel doesn't let you DELETE records like this."

     

    Wht not create a new spreadsheet instead of trying to delete all of the records?

    Thursday, March 27, 2008 1:16 PM
  •  MatthewRoche wrote:

    I think this error probably means "Excel doesn't let you DELETE records like this."

     

    Wht not create a new spreadsheet instead of trying to delete all of the records?

     

    That is correct, Jet provider does not support neither truncate or delete. You have 2 options:

    • Have an empty excel template that you clone before the running the dataflow, or
    • Use execute sql task to create a new workbook/tab before running the dataflow
    Thursday, March 27, 2008 1:47 PM
  •  

    Hi Matthew and Rafael.

    First of all thanks for your interesting.

     

    I try to clone excel file. But i don't know how do i this using SSIS.

    Can you give an advice to solve this problem.

     

    Thursday, March 27, 2008 2:40 PM
  • Use the File System Task to copy the XLS file from your template location to the location where your data flow needs it to be.

    Thursday, March 27, 2008 2:50 PM
  • hi.

    I solve my problem with your help.

     

    First i use File System Task which copies blank excel file.

    then i use Execute SQL Task and it creates new sheet my excel file.

    then Data Flow Task ...

     

    Thanks

    Friday, March 28, 2008 1:40 PM
  •  koray kocabas wrote:

    hi.

    I solve my problem with your help.

     

    First i use File System Task which copies blank excel file.

    then i use Execute SQL Task and it creates new sheet my excel file.

    then Data Flow Task ...

     

    Thanks

     

    Glad we could help - thanks for letting us know!

    Friday, March 28, 2008 1:42 PM
  •  koray kocabas wrote:

    hi.

    I solve my problem with your help.

     

    First i use File System Task which copies blank excel file.

    then i use Execute SQL Task and it creates new sheet my excel file.

    then Data Flow Task ...

     

    Thanks

     

    The Execute SQL task would do both; create the file and the tab; no need of the File System task. File System task is helpful in scenarios where you the Excel target requires some preformatting , or a predefined number of tabs that is available in a template. That is why I said either/or in my first post.

     

    Anyway, I am glad it is working.

    Friday, March 28, 2008 1:49 PM
  • My problem is similar and tried the File System Task  to copy from a template file.  Is there a way Execute SQL Task (to prepare the Excel file) can help me do the following:  export data from a SQL table into an Excel file which will be overwritten daily.  

     

    When I used the Execute SQL Task with the command of "CREATE TABLE `Query` ..." it failed after the 1st run because the table already existed.  So I tried the DELETE FROM `Query` and I got an error message that this is not supported.

     

    Any tips would be helpful!

     

    Saturday, April 05, 2008 12:22 AM
  • hi fudge.

    if you use delete query , you have an error.

     

    I think (step by step)

    you should delete excel file (file system task)

    create excel file (file system task)

    create excel sheet (execute sql task)

     

     

     

    Saturday, April 05, 2008 7:26 AM
  • although excel records cannot be deleted using jet, they can be deleted using ole db via a linked server using the t-sql openquery statement: http://msdn2.microsoft.com/en-us/library/ms188427.aspx

     

    hth

    Saturday, April 05, 2008 8:31 AM
  • Thanks.  This is what worked for me:

    File System Task - to copy from a template

    Execute SQL Task - to create the 'table' spreadsheet

    Data Flow Task

     

    The SSIS package kept failing at the copying of the extracted records into the Excel file without the Execute SQL Task even though the spreadsheet already existed in the spreadsheet.

     

    Anyways, thank you for the help.  I'll try the linked server recommendation when I get a chance.

     

     

    Friday, April 11, 2008 3:32 PM
  •  

    Hello,

    I have a similar issue that I hope is a simple one.

     

    After you use the ExecuteSQLTask to create the new tab, how can you use it?  Inside the DataFlow, Excel Destination, it seems to expect to see the tab already in the destination worksheet.  How can I tell it to stop checking for the existance of the tab?  It doesn't exist as I'm writng the package, but it will be there by the time the Data Flow executes.

     

    Thanks,

    JB

    Stevens Point, WI

    Friday, May 02, 2008 4:50 PM
  •  

    Place the sheet(table) name is a variable; then In the Excel Destination Component, Change the data access mode to ‘Table Name or View Name Variable’ and choose the variable name from the drop down list.

     

    http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html

     

    Friday, May 02, 2008 5:13 PM
  • Rafael,

     

    Excellent.  Thank you.  The "delay validation" was the key.

     

    Regards,

    JB

    Friday, May 02, 2008 6:50 PM
  • I was able to get a quasi-delete to work sort-a-speak. I created 2 Execute SQL tasks. The first one does a drop table on the "excel_destination" table. The second one creates the "excel_destination" table. This basically creates the excel file. I then use a data flow task to copy data from SQL Server to the Excel file. I got the table name for excel from the Destination excel task.

    I've added some variables and everything works like a champ. :)

    Wednesday, January 28, 2009 2:15 AM