Re: Drop & Create Table - DTS Export to Excel

From: Toney (Toney_at_discussions.microsoft.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 15:19:01 -0800

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 work***/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:

> 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 work***.
>
> --
> --
>
> 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@discussions.microsoft.com> wrote in message
> news:D4F2D04B-00C4-417B-BD13-03DC50E8841F@microsoft.com...
> > 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.
>
>
>