Re: Drop & Create Table - DTS Export to Excel

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 11/05/04 

Date: Fri, 5 Nov 2004 06:17:31 -0000

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@discussions.microsoft.com> wrote in message 
news:3752B7D6-9D74-4786-8479-006794ECC875@microsoft.com...
> 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.
>>
>>
>>