DTS File Overwrite Question 1

[Nassy]

Hi

I have designed a package that creates a table and then exports the results to a spreadsheet.

The problem is, each time I run the spreadsheet the new results are appended to the old results. What I want my DTS to do is to completely overwrite the spreadsheet each time I run the export

Any help will be much appreciated on this as I have really hit a brick wall!!!

Nassy

 

[ecobb]

Each time the DTS runs, are you deleting the previous DTS info. from the table? If the info. is still in the table, then you will either need to delete it (if you no longer need it) or specify to overlook it when exporting to your spreadsheet.

Hope this helps!

 

[JayKusch]

Had a similar situation. If you created the DTS package strickly though the design application, I could not see the option to "Drop and ReCreate Destination Table" which was my excel worksheet.

SO, If you use the DTS Wizard to start your package creation, it will give you the "Drop & ReCreate ..." option as part of the Transformation table of the export.

Once you have that in place, you can go back into design mode and modify the package to do any other routines that may be needed.

If someone knows where the "Drop & ReCreate" option is within the DTS design application, by all means let us in on the secret.



Thanks

J. Kusch

 

[Nassy]

Hi

Thanks for the reply.

The problem is that when I rerun the data export I want all the data in the spreadsheet I am exporting to, to be overwritten. The DTS transformation task did not offer me this option.

I have tried to get around the problem by using DROP TABLE and CREATE TABLE commands but I can't get this to work as the syntax created for a spreadsheet table is different to an ordinary table eg create table `mytable`. If I try to use this code in a task SQL Server complains bitterly.

What I am trying to do now is to create a batch file which will overwrite my Excel spreadsheet every time.

Nas

 

[Nassy]

Thanks for the suggestion of using the Wizard. Creating a batch file is a bit fiddly so I will give this option a go!

 

[JayKusch]

Let everyone know how it works out!

Thanks

J. Kusch

 

[Nassy]

Hmmm...

I don't seem to be having much luck!

I did try the Data Import/Export Wizard but did not notice any option for 'overwrite'.

So I managed to write a batch file which replaces my spreadsheet with data in it with an empty spreadsheet with the same name.

However despite the new empty spreadsheet having the same name as the old one with data in it, SQL Server seems to force me to reset the link to the spreadsheet. If I don't do that it doesn't recognise that the spreadsheet is even there!

It is such a shame that the DTS designer forces you to pre-select a spreadsheet and does not give you the overwrite option. At the moment this is causing me big problems!

Nassy

 

[JayKusch]

OK ... maybe I can point you in the right direction here.

1. From Enterprise "Mangler" menu bar pick Tools.DTS.Export

2. Click Next and Choose Your Source then click Next

3. Set your destination to be MS Excel 97-2000, enter your desired filename and the click Next

4. Set your Table Copy or Query Option, click Next

5. Select your source table and when you do, your excel filename should pop up in the "destination" column of the page.

6. Click on the "transformation" elipse (...) and on that page you will see the option to "drop and recreate destination table". This will drop and recreate your excel worksheet.

That should do it!


Thanks

J. Kusch

 

[Nassy]

Thanks for the reply. I have finally found the option for drop or create table so thanks for your advice. I have to say it is pretty well hidden and I am not surprised I didn't see it the first time!

Thanks again

Nas