Hi,

I am trying to export some records everyday to a destination (.xls) file
through SQL Server Enterprize Manager. The DTS package works fine .... BUT
the problem is every time the DTS package runs the records get appended in
the .xls file.

HOW CAN I MAKE SURE THAT THE .XLS FILE IS OVERWRITTEN EVERDAY .... SOMEONE
PLEEEEASE HELP ME.....

Regards.

So here is what works for me using Excel 2003

I have an ExecuteSQL task which does a DROP TABLE against the XL connection
I have an ExecuteSQL task which does a CREATE TABLE against the XL connection
I then have the Transform Data task that puts data into the file

Help any?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote

Thanks a lot,Allan Mitchell.

I was succesfull in DROPPING TABLE against the XL connection
by using the code:
exec master..xp_cmdshell "del j:\my_delete.txt"

BUT
I was unable to CREATE TABLE against the XL connection.

Can u please help me with the code for creating table against the XL
connection.

Best regards.

--
Message posted via http://www.sqlmonster.com

Drop onto designer an ExecuteSQL task
In there put

DROP TABLE 'MyExcelTableNameGoesHere'

You can then issue a CREATE TABLE statement as well in a seperate ExecuteSQL task





--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

DROP TABLE 'MyExcelTableNameGoesHere'

would not work for me ... But the following worked for me:

exec master..xp_cmdshell "del j:\MyExcelTableNameGoesHere"

Now I need help with the codes for:

CREATEING TABLE against the XL connection

Best regards.

--
Message posted via http://www.sqlmonster.com

You are talking about deleting the whole Spreadsheet. I am talking about deleting a worksheet/Name Range/Table within a spreadsheet

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Ray via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Yes, u got it ... I am talking about deleting the whole Spreadsheet.

Is it possible to create a fresh spreadsheet against the XL connection in
the DTS package.

Thanks for your help!

Regards.

--
Message posted via http://www.sqlmonster.com