Not sure where to post my question, so I applolgize if this is in the wrong forum.
I have a DTS job that runs a sql query to join 3 tables to pull data needed for an excel spreadsheet and then on completion, the excel spreadsheet is emailed to a user.
My problem is that this runs multiple times a day and currently, the excel spreadsheet is just appended to. I need it to NOT append but to create a new excel spreadsheet. If I manually remove the excel spreadsheet, then DTS fails indicating that a temp table(created by DTS for the excel spreadsheet) does not exist.
How do I create the spreadsheet from scratch each time the DTS is executed?
Create a SQL task who’s existing connection is the excel spreadsheet. Remember, the sheet “screwup” is treated like a table so you must drop it and re- create it, so type in “drop table screwup go” and now paste your create table syntax
I tried the above suggestion of wildh and get an error on the drop table:
"Cannot drop table 'Screwup', because it does not exist in the system catalog"
The Excel file was created with the 2 records as described. What am I missing?
Never mind, the SQL task had the wrong connection. It works now, if the Excel file exists. Is there a way to check if the file exists before dropping it? I tried : IF NOT (OBJECT_ID('Screwup') IS NULL) DROP TABLE Screwup
but I get an error. Where can I find the syntax I can use here?