Delete rows in Excel From DTS package

I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than replaces.

I can't delete the excel files on a daily basis, as they have to be
there for the DTS package to be able to export to Excel. What I want
to do is create a VBScript (ActiveX Control) to delete all the rows of
data except the first row within each spreadsheet as the first step of
the DTS package. Then the remaining steps would run and the
spreadsheets would only have the current day's data at the end of the
process.

Thanks for any help offered.I would recommend using a pull rather than push strategy with Excel.

Have your DTC package deposit the data in a report table and then embed
queries in the Excel spread sheet to grab that.
Your users could just hit the refresh button on their spreadsheets to get
the latest and greatest data (or write VBA script in the open even to
refresh it auto-magically).

You may be able to invoke the Excel App from the DTC script to get it to
call the refresh function as well.

We had the same issue with exporting to Excel. My crackpot theory was that
it was using the Excel ODBC driver, which appears not to be able to rewind
when streaming data into a spreadsheet. You may be able to delete the rows
to reset the spreadsheet in a separate operation from adding the new rows,
or do what we ended up doing - having DTC do a file-copy to overwrite a
template on-top of the target spreadsheet.

Happy Trails ...

<smonczka@.hotmail.com> wrote in message
news:1109285580.060720.12490@.z14g2000cwz.googlegro ups.com...
>I have a DTS package that needs to refresh data in 3 separate Excel
> spreadsheets on a daily basis. The problem is that unless I manually
> delete the previous day's data, it appends rather than replaces.
> I can't delete the excel files on a daily basis, as they have to be
> there for the DTS package to be able to export to Excel. What I want
> to do is create a VBScript (ActiveX Control) to delete all the rows of
> data except the first row within each spreadsheet as the first step of
> the DTS package. Then the remaining steps would run and the
> spreadsheets would only have the current day's data at the end of the
> process.
> Thanks for any help offered.

Delete records in SQL table, which are present in Excel

Hi everyone,

I have a task to create a dts package, which will delete records in a SQL table, which are present on a sheet tab in Excel. I know how to transfer records from Excel to SQL table and vice versa, but not sure how I can delete the records in SQL table, which are present in Excel. Note that there are just 2 columns - one is a key and the other name.

Thank you all in advance,
SauravI would NEVER recommend using Excel as source data for a relational database. You are begging for data corruption.

At the very least, load the Excel data into a staging table within your database where it can be cleansed and verified before you start deleting production data based upon its contents.

Bad idea. What is the source of the Excel data?