I had originally posted this in the OLE DB forum, but
perhaps that was the wrong place so I'm post here as well.

Does anyone know how to force an SQL DTS package to
populate the results/output of an SQL select statement to
a specific cell in an Excel spreasheet every time?

My DTS package executes successfully each time (at this
point I am only testing with a single record of output).
package is set up to drop the existing table on the Excel
sheet and to create the table new each time. However,
instead of populating the query results to cells $A$2:$E$2
each time, it instead populates the results in the cells
below the last cell in which the DTS package had placed
the results from the most recent other run.

In other words, the 1st run of the DTS places results in
cells $A$2:$E$2. The second run will delete all cell
values in $A$2:$E$2 and instead populate the results to
$A$3:$E$3. Third run wipes $A$2:$E$3 and places results
to $A$4:$E$4, etc.

Is there any way to have the output always start in $A$2?
Please help.

Scott

How are you dropping and recreating the table in Excel?

I just used

DROP TABLE <table Name> in an ExecuteSQL task
followed by
CREATE TABLE <table name><table def> in another ExecuteSQL task

And it does what it says (Office 2003)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Scott" <Scott (AT) discussions (DOT) microsoft.com> wrote

Hello Allan,

Thank you for your reply. 1st, I'm working with SQL
Server 7.0 installed on a small server, and have the SQL
client tools installed on my machine running W2Kpro with
Office 2000.

My DTS flows as follows;
Step 1: Execute SQL task DROP TABLE <tablename> -->(on
completion goto step 2)
Step 2: Execute SQL task CREATE TABLE <tablename>
(tabledefs) --> (on success goto step 3)
Step 3: Connection1 to mySQLserver&databasename
Step 4: Data Transformation Properties window;
Source tab: SQL query to "select * from <othrtable>"
Destination tab: store results of transformation
in table created in step 1.
Transoformations tab: copy column - 1 to 1 copy
of fields from source table to destination table.
Advanced tab: defaults (1st row = 0, Use fast load,
check constraints)
End at Connection 2 (to the excel workbook where the table
goes)

The table name in the drop and create steps 1 & 2 is the
same name as the excel sheet with a '$' added,
i.e., 'sheet1$'.

So when you repeat the DTS each time yourself, the results
are always populated below the field headings beginning in
cell A2? Why is mine not working like that?

Scott

I am experiencing the same thing and not finding any answers. Did you ever
get a working solution to this problem?

"anonymous (AT) discussions (DOT) microsoft.com" wrote:

Me too.
I do some test exporting data from one EXCEL file to SQL SQEVER by using
Enterprise Manager export wiard
and save as a DTS package, it works. But the custom package I do doesn't
work. The result always append.
Is there any connection parameters I should set ?
Any ideas is appreciated.

Thanks


"Toney" <Toney (AT) discussions (DOT) microsoft.com> ¦b¶l¥ó
news:2AD6B5F1-852C-49E5-B503-F852B8E83028 (AT) microsoft (DOT) com ¤¤¼¶¼g...

It would seem that nobody has an answer as to how we can solve this problem
or are unwilling to share it with the rest of us. I have done extensive
searches throughout the web to find the answer. I am astonished at the
number of people who are having this problem. I am more astonished by the
fact that there is no attempt to answer the pleas for help. The only
responses given are that "it works as it should...". But, clearly, for many
of us it is not working as it should. It also appears that Microsoft's own
Knowledge Base contains one single article relating to this issue. I have
reviewed the article and found that I did, in fact, create the DTS package
correctly... but it still produces the same result.

I ran across one thread in this group - started by the same person who
started this one - that ended with "...I figured it out..." but that person
failed to share their solution/findings. IF YOU HAVE AN ANSWER AND/OR
SOLUTION, PLEASE SHARE IT WITH THE REST OF US.

Toney

Because I did not see the atsrt of this thread and you have not included it
I shall infer the following.

You are exporting to Excel and you APPEND when you want to REPLACE

So here is what I did


First Task: ExecuteSQL task of DROP TABLE X
Second Task: CREATE TABLE X
Third Task: DataPump into Table X

I executed authors (pubs) into this table 4 times in a row and only had 23
rows in the worksheet.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Toney" <Toney (AT) discussions (DOT) microsoft.com> wrote

Allan, thank you for your response. I can get the Excel tables to drop and
can successfully create them again. The problem I'm having is that the new
data is appended to blank rows starting at the row following the last row of
the deleted data. That is to say that on the initial execution of the
package, the data fell into place perfectly and as expected. For the
purposes of this discussion, the last row of data was row 100. On the next
run of the same package - which dropped and created new tables - the
resulting table had 100 rows of empty data with the first row of new data
starting at row 101. A subsequent execution of the package resulted in a
table containing 200 rows of empty data with the first row of new data
starting at row 201. This pattern continues with each subsequent execution
of the package.

However, if I open the Excel file and manualy delete all affected rows and
then execute the package everything is perfect and the data is written to row
1 (actualy row 2 as I have column headers in the worksheet/table). But,
unless I open and manualy delete all rows of data, the patern repeats itself
as outlined above.

I guess the most frustrating thing for me is that I don't understand which
application is causing this to occur. Is Excel retaining "row markers" and
preventing the package from writing to "used" rows or is SQL? Neither make
sense to me in light of the fact that the tables are dropped and created new
with each execution of the package. I should point out that the new tables
are created using a copy of the same Excel document with empty rows as a
feed. I have verified that the "feed" document contains no data and, in
fact, has never contained data other than formatted column headers.

I sincerely appreciate your help on this and will gladly provide any
additional information you may require.

"Allan Mitchell" wrote:

What versions of what are you using.

I believe it is the driver that does APPEND

I have seen this so let me try to recreate the problem you are having
because as you can see I do not have that problem right now.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Toney" <Toney (AT) discussions (DOT) microsoft.com> wrote

I am using SQL2000 SP2 and Excel 2002 SP3.

Thanks again for your help.

"Allan Mitchell" wrote: