Samuel Haddad

Just thot of trying ur suggestion…..but facing a problem as below….

What would be the best way to provide the table name in this qry ??

ERROR
[Execute SQL Task] Error: Executing the query “DROP TABLE Incident.xls” failed with the following error: “Syntax error in DROP TABLE or DROP INDEX.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

The correct Syntax would be something like
DROP TABLE `Excel_Destination` SSIS uses those weird ` characters, it might be best to double click on your Excel destination try creating a new sheet then copy and paste the syntax just renaming create to drop.

This was a big help in an SSIS project I just completed. If you don’t mind, Here’s a little syntax for the CREATE table statement that creates a range called “employee” in excel with the following columns:
CREATE TABLE `employee` (
`emp_id` NVARCHAR(10),
`Last Name` NVARCHAR(30),
`First Name` NVARCHAR(20),
`Logon ID` NVARCHAR(50),
`Email Address` NVARCHAR(100),
`Office Location` NVARCHAR(3)
)

Nice Article! I have everything working except that the data is appending to my spreadsheet instead of overwriting it each time? What am I missing?

Matt, Glad you liked the article. You have to go into the control view of SSIS, and setup the SQL tasks to drop the table and recreate it. The excel destination does not have an overwrite feature by default. If you are getting stuck at a certain part let me know.

Hey Samuel, I got problem. I think you got answers to this question. I have excel sheet which is used for reporting. I have to update the excel column from sql server 2005. The problem here is that i can’t drop the table(in excel) as u suggest because i have used function linked with each column on the excel sheet to creat pi chart and other reporting charts. So How can i update each column without affacting those functions in excel sheet.

It doesn’t work.
You can’t perform [drop table sheet1] or [drop table UnusedIndexes] statment on excle sheet.
I’ve tried 13 times it doesn’t work.
I AM JUST DELETING A SPREADSHEET IN A UNUSEDINDEXES FILE.

I moved step by step… It doesn’t work.

//————-See below error———

SSIS package “Package.dtsx” starting.
Warning: 0×0 at Execute SQL on Sheet: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Warning: 0×0 at Execute SQL on Sheet: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Error: 0xC002F210 at Execute SQL on Sheet, Execute SQL Task: Executing the query “Drop Table UnusedIndexes” failed with the following error: “Table ‘UnusedIndexes’ does not exist.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL on Sheet
SSIS package “Package.dtsx” finished: Success.

Thank you so much for the help i was actually cracking my head for this, cause i knew the flat file connection can just overwrite the text, then how could i overwrite data in my excel file.
But this has been verry helpful

Thanks so much

Khurram Nazir

Well i have tried and i works.

First of all make sure that the table you are trying to delete is present, which means, at first step go to your dataflow task and create the table in the excel destination,
then after that,you can then delete the table and recreate it again in the second sql Task( with the same synthax you used in the excel destination)

and make sure that on the drop Table task dont use the parenthesis

example if you created a table

CREATE TABLE ‘user’(
‘userID’ nvarchar,
‘name’ nvarchar)

then the DROP statement will be

DROP TABLE user and not DROP TABLE ‘user’

Hope this will help you

This is great information. Thank you for publishing it.

The issue I am running across seems to be with the first step with the drop table.

I have the following drop statement (the CardSwipe tab does exist in the spreadsheet):

drop table CardSwipe

I get the following:
Error: 0xC002F210 at Excel Drop Table CardSwipe, Execute SQL Task: Executing the query “drop table CardSwipe” failed with the following error: “Table ‘CardSwipe’ does not exist.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

I have another spreadsheet version where it does not delete the table, and does not fail, but I end up with a tab named CardSwipe1 inserted next to the CardSwipe tab. This says to me that it is obviously not deleting the tab before the new tab is inserted. It seems this is a tempermental process.

Any ideas on what I might be doing wrong with the drop?

Thanks,

Earl

Very well done., very useful.

Thanks for uploading

Well done. Thank you very much for sharing.

But what I was trying is to create dynamic worksheets with names taken from an SQL table.

Hope somebody could help!

Thank you very much! Very useful!

Thanks for sharing.

I’ve tried it. But it only drop the header(column created) of the excel file not the content of the excel file. How to fix this problem?

Thanks for help.

I also tried DROP TABLE `Sheet1` but gave me an error so then I tried
DROP TABLE `Sheet1$`. My Execute SQL Task was successfully completed but it actually didn’t delete the worksheet from the excel file.

I’m using Excel 2007 & SSIS 2008.

Any other suggestions please?

I cannot get the EST, DROP TABLE command to work. No mwaater the eyntax I try, it throws the error `Worksheet name` does not exist.

Please help.

The Drop Table statement only deletes the headers, and the Create Table statement only populates the headers. The Dataflow task always appends new rows. Why?

This is really slick. Everything works as described except one thing. When I populate the spreadsheet with lets say 50 records, and then run the SSIS Package again – it drops successfully, it creates successfully and even populates successfully but looking at the spreadsheet the first 50 rows are blank and starting with position 51 is where the 50 records now reside. Using Visual Studio 2008 with Excel 2007. Same results if I switch to Excel 97-2003. Not sure of any additional settings I could apply.

Barry,

I am no longer working on SSIS projects and do not have access to an installation to test on. The best I could do is look at your statments and see if something stands out. Post it here if you want me and others to take a look.

Hi Barry,
how would i ask if it exists, drop the table?

Jennifer

Whoops, I meant Samuel, not Barry

Thanks, you saved me a headache.

Hi , when i tried to use the
DROP TABLE `SheetName`
It is just deleting the header rows,leaving the other affected rows.
My data is getting piled up everytime and getting duplicate records.Can you please help on this.

Thank you very much. It’s very very useful & help me a lot

Very nice, this saved me some nasty VBA

Thank you. After 90 minutes of looking for a solution to not being able to just overwrite an excel file, I came across your solution.

It works very well for my need.

you can do it in much simpler way, All you need to do is the following.
1.Have your excel template ready in some location with the same name of excel file in which you are having it as your destination .
2.Create a file system task to replace/copy type as template excel the source and your actual excel as destination.
3.Remaining dataflow task keep it as it is .

P.S:- File system task will be your first task before dataflow, you can also have a sql task if you want to create an excel in between those two, but depends on your requiremnet in my cas i didnt need it.

let me know if any queries

Cheers,
Aashish

hi i want now this excel file to be stored in table each day
how to do that..?