DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.
Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John
|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John
|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John
|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John
|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c
|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John