Excel problem

I am trying to import data from sql server to excel.

it creates a new worksheet with name 'mytable' and excel file also has 3 sheets (by default as well).

when package is executed, Data gets transferred first time.

When I try to execute package again it gives me an error - that Table 'mytable' already exists.

To solve this I added another task before it creates the table ('mytable' sheet in excel), where I drop this table with the statement " DROP TABLE 'mytable' " (Connectiontype is EXCEL)

it works now, but I need to have this table 'mytable' in the excel, when ever I need to execute the package.

Is there any statement like in sql where I can check whether the table exists or not - like " IF EXISTS( select * from sysobjects where name = 'mytable')
I need to check this in Excel.

also, is there a way to drop other 3 sheets in excel, which comes by default.

Thanks in advance.

Management Studio export wizard after selecting source and destination.In specify table copy or query pane select query write query click next and click mapping button at select dource tables and views pane.Configure destination excel sheet.

bye.

|||

The Excel driver respects the saved Excel setting for the default number of sheets in a new workbook. One way to avoid this is to create an empty "template" workbook configured as you want, and use a File System Task to make a copy of that template each time that you want to perform your export.

Or, you could use Execute SQL Tasks to repeat both the DROP and the CREATE each time, and configure error settings such that, if the DROP fails (because the table does not exist), the package continues.

-Doug

|||I was able to do it by the first method, by creating an template workbook.

I tried to work by second method by checking whether table (worksheet) exists in excel or not, but couldn't get it through due to syntax error. for excel database connection, I didn't get it through the corrent syntax.|||

IF EXISTS certainly won't work with Excel.

If you need to check for existing tables, the System.Data.OleDb namespace has some methods to return schema information that Jet/Excel may support. (I haven't yet tried it myself.) You could connect in a Script Task, check schema information, and set a package variable value to indicate to downstream tasks whether the intended destination table exists or not.

-Doug