SSIS drop table on spreadsheet

Question

Post reply

SSIS drop table on spreadsheet

Trybbe

Hall of Fame

Points: 3967
More actions
December 10, 2012 at 4:06 am

#267295

Hi There pls assist.
I am trying to create a package that will query sql server and return results to excel, this package should replace existing data on the excel with new data everytime it runs.
currently I tried using two sql tasks one to drop the table and one to create. when I run the drop table task it only clears the headings.
please assist.
Kind Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

How about TRUNCATE TABLE or DELETE * FROM TABLE ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

kl25 Hall of Fame Points: 3752 More actions · Answer 2

If all of the data on all of the tabs in the file are refreshed each time, you might consider dropping and recreating the excel file with each run.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

You could also create a "template" so to speak, with the necessary tabs with column headers only, from the resulting spreadsheet, and copy it over the exsting spreadsheet each time in a prior step.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

j.miner SSCrazy Points: 2264 More actions · Answer 4

I agree with sgmunson. Back in the day when I did alot of DTS work, both the truncate table and delete from table would work fine. The syntax for the name of the tab was a little weird. Just google around.

Good luck

John Miner

CraftyDBA

http://www.craftydba.com

John Miner
Crafty DBA
www.craftydba.com

Trybbe Hall of Fame Points: 3967 More actions · Answer 5

Hi Guys,

I tried delete * from table_name, but I get this error :

[Execute SQL Task] Error: Executing the query "delete * from Data_Destination" 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.

I tried truncate table table_name, but I get this error :

[Execute SQL Task] Error: Executing the query "truncate table Data_Destination" failed with the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

The Excel SQL syntax (or whatever it is called) is more like this:

DELETE * FROM [mySheet$]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Trybbe Hall of Fame Points: 3967 More actions · Answer 7

Thanks for the feedback I went with deleting and recreating the file and it worked fine. thanks everyone

Prometheus112 Mr or Mrs. 500 Points: 503 More actions · Answer 8

I know this post is old, but for anyone stumbling onto the forum looking for a way reset the "Name of Excel sheet" inside of the Excel Destination - you cannot use DROP TABLE [tableName$];. What you have to realize is that the CREATE TABLE statement you made by clicking "New..." actually created a spreadsheet at the destination. You have to delete the spreadsheet before you on the file system/windows explorer before trying to recreate a new table. This often happens when your first attempt uses the wrong Data Type in Excel, and you get the error "Cannot convert between unicode and non-unicode string data types".