Excel in DTS

Question

Excel in DTS

Hollyz

SSCommitted

Points: 1763
More actions
June 15, 2006 at 2:24 pm

#67454

I know how to drop a table (a sheet in excel file).
DROP TABLE name_of_table
GO
But how do you type the syntax to check if the table exist? (this table again is not a sql server table, but a excel table).

I want to put this logic in the DTS Package SQL Task.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Greg Baethke Default port Points: 1442 More actions · Answer 1

The easiest way to do this is to use a scripting task and instantiate a file object to see if the file exists. Then instantiate an Excel object and see if the worksheet exists.

To do this in a SQL task would be a bit more difficult because you would have to issue command line shell to do this.

Searching Excel automation tasks will give you syntax examples for the scripting task.

Hollyz SSCommitted Points: 1763 More actions · Answer 2

But I don't know too much about scripting task. Can you give me a good example?

I do recall I have seen the 'IF EXISTING '... in a sql task in DTS Package.

LChao SSC Enthusiast Points: 131 More actions · Answer 3

You can do a select against the table. If the table does not exists you'll get an error, invalid object name 'MyTable'. Trap for this error in your code and if SQL Server generates this error you know it does not exist.

Hope it helps.

LC

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 4

This is what you need to know right here!

http://www.sqlservercentral.com/columnists/jsack/capturingtheerrordescriptioninastoredprocedure.asp

[font="Courier New"]ZenDada[/font]