Deleting from an Excel doc with a DTS

[MikeMcKeown]

Hi,

I have set up a DTS package which links to an Excel document. This document is going to be updated on a weekly basis.

However the problem is that when I run the query, the rows just append to what is already there and I would like to replace them.

I then tried to use an SQL Task in the DTS package designer where I selected to delete the rows in the Excel sheet and then I was going to add the new rows.

However when I run the package, I get the following error:-

Deleting Data in a linked table is not supported by this ISAM.

I am however able to write to this Excel document.

Any ideas??

 

[jgoodman00]

How about creating a new worksheet each week, instead of overwriting it?

It might also be possible to script the deletion of the existing data, but I have not done this. I have always been able to delete the existing & replace that way...



James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[MikeMcKeown]

Do you mean that you can automate the deletion of the Excel file or that you manaully delete it??

 

[donutman]

Have the package runs a batch file that copies an Excel file that is to be used as your template.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jdgonzalez]

What I usually do is to create the excel object at runtime and load the data to the new create file and then change the name of the file to maintain a history of the data.

 

[MikeMcKeown]

Thanks for that - do I create the file at runtime in the DTS package and if so how would I do it.

 

[jdgonzalez]

This is how I did it. If there are better ways of doing this please let me know, but for now I'll stick to what works.

1: Create the Excel connection. I create it with a temp name like <<jobname>>_tmp.xls.
2: Create SQL server connection (if that's what you're using to get your source data).
3: Use an 'execute SQL task' to create the fields you want to appear in the Excel doc (of course, using the Excel connection you just created).
4: Create a transform task between the SQL Server conn and the Excel conn. Place your SQL logic there. You may need to execute the previous step so that there is a file with an appropriately named tab when you configure the destination settings.
5: Create an active X script that will take the file name for the excel file you specified earlier to change the name something else.

The order of events is a bit different than how you created them:
Step 1: Number 3 from above
Step 2: Create an 'on success' workflow to SQL Server conn
Step 3: Create an 'on success' workflow to the Number 5 from above (active x)

Hope it works out.

 

[MikeMcKeown]

Thanks for that

Will try that and post back results

 

[togatown]

Mike,

I'm pretty new to this but......

Create your Transform Data Task from your SQL Connection to Excel.

When you create the destination table in the worksheet (Tranform Data Task Properties dialog, Destination tab), copy the create table query.

Test your output.

Create an Execute SQL Task, decription 'Drop'. Select your xls connection as the target the SQL query is drop table `yourexceltable`.

Create a second Execute SQL Task, description 'Create'. Select your xls connction as target and paste the create table query from your Destination.

Right Click on Create and select Workflow. Click New, select Drop as your source, Completion as your precedence and Create as your Destination.

Right Click on your Transform Data flow arrow and select Workflow Properties. Click New, select Create as your source, Success as your precedence and whatever you named your transform data task as your Destination.

Save and done.

Sorry for any typos, it's late here.

Toga

 

[dbomrrsm]

if you do the export to a text file it will overwrite last weeks file not append and if you create the text file as a csv that can be opened directly by excel.

[blue]DBomrrsm[/blue]