Hi,
I have data going from SQL to an Excel file with a DTS package. Each time I
run this is adds the data to the existing data in the spreadsheet (that was
there from the last time I ran it). How can I make it overwrite what is in
the spreadsheet instead of append to it? Or better yet, is there a way to
have it create an entire new spreadsheet, automatically, each time. I want
to get this as automated as possible so on a given date it will pull the
data from SQL and create a new spreadsheet. Can that be done?

Thanks,
Dan

Personally I drop and recreate the Excel "Table" before i do the datapump
each time

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Dan B" <none (AT) none (DOT) com> wrote

I tried this too and it just appended the data to what was already there.
It would not drop it. What am I missing? Is there some sample code or
something you could post?

Thanks,

Dan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Using a Datapump between SQL Server and Excel.

Authors table in Pubs --> Excel

First Task:

ExecuteSQL task
Excel Connection
Text

DROP TABLE Authors

On Success

Second Task:

ExecuteSQL task
Excel Connection
Text

CREATE TABLE authors (
au_id VarChar (11) ,
au_lname VarChar (40) ,
au_fname VarChar (20) ,
phone VarChar (12) ,
address VarChar (40) ,
city VarChar (20) ,
state VarChar (2) ,
zip VarChar (5) ,
contract Bit )

On Success


Third Task:

DataPump Task.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Dan B" <none (AT) none (DOT) com> wrote

From SQL Help File
spreadsheet, transforming it into a Microsoft PivotTable®. The program also
demonstrates how to create a Data Transformation Services (DTS) object.

Default Location
C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\Dts\Dtspackages\D tsexmp2

Running the Sample
Here are the steps for running the Cptexmp application:

1.. Open and compile Cptaxdll.vbp.
This registers the CreatePivotTable custom task used by Cptexmp.vbp and
tstuiapp.vbp.

2.. Open and run Cptexmp.vbp or tstuiapp.vbp.
Hope it helps
Bruno Alexandre
(Sintra, PORTUGAL)
"Dan B" <none (AT) none (DOT) com> escreveu na mensagem
news:eSskC7ejDHA.2000 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

This is exactly what we have set up. It is working except for one thing.
It drops the data out of the table in Excel, but the next time I run it, the
data gets dumped below where the previous data was. So, in my Excel sheet,
after running this a second time, I have 200+ blank rows, then the data
shows up. Running it a third time, I will have 400+ blank rows, then the
data.
Any other ideas as to how to fix this?

Dan


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote