SSIS drop table on spreadsheet

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SSIS drop table on spreadsheet

SSIS drop table on spreadsheet

Rate Topic

Display Mode

Topic Options

Author

Message

Trybbe

Posted Monday, December 10, 2012 4:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:04 AM
Points: 169, Visits: 433

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

Post #1394529

sgmunson

Posted Monday, December 10, 2012 6:52 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

How about TRUNCATE TABLE or DELETE * FROM TABLE ?

Steve
(aka sgmunson)
Smile

Weight Loss Tips

Post #1394588

kl25

Posted Monday, December 10, 2012 9:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 241, Visits: 1,099

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.

Post #1394674

sgmunson

Posted Monday, December 10, 2012 10:09 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

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)
Smile

Weight Loss Tips

Post #1394700

j.miner

Posted Monday, December 10, 2012 6:12 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 79, Visits: 280

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
www.craftydba.com

John Miner
Crafty DBA
www.craftydba.com

Post #1394839

Trybbe

Posted Monday, December 10, 2012 11:36 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:04 AM
Points: 169, Visits: 433

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.

Post #1394905