DTS package to delete all rows from excel

Question

DTS package to delete all rows from excel

Hollyz

SSCommitted

Points: 1763
More actions
August 18, 2008 at 1:39 pm

#70645

I have a dts package in sql 2000.
Connection is Excel file (Microsoft excel 97-2000).
SQL Task which points to the Excel file connection: DROP TABLE `Name_of_theExcelsheet`;
2nd SQL Task to create it back: CREATE TABLE `Name_of_theExcelsheet` (etc....)
3rd task--> DTS Transformation : SQL statement into excel file.
When I run the DTS package manually or using the sql job, the Task runs fine, the excel file does capture the necessary data.
One problem, the excel file never get clean up. It just appends.
I thought the DROP TABLE statement should take care of it.
I tried: DELETE From `Name_oftheExcelsheet`;
That too didn't work, but I got no error.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

Are the columns the same for the new and old excel file? you can try creating an excel file that will stand as your template save it to a location then just copy it to the folder where you will need to insert the data. You will not need to clean the data in the excel file all ou have to do is to overwrite the file with a new one.

"-=Still Learning=-"

Lester Policarpio

Hollyz SSCommitted Points: 1763 More actions · Answer 2

I tried your way which sounds very good.

I still got a problem, when the dts package runs, the excel file remains empty (except for the column header) even though the dts package says 150 rows was executed.

Very strange and getting stranger.

Hollyz SSCommitted Points: 1763 More actions · Answer 3

Also, I realize when the DTS Package runs (without doing the template copying over),

Example, if 150 rows should be the output on excel file, the 1st time it runs, it has 150 rows.

So after the 2nd time the DTS runs, it'll say 150 rows executed.

When examing the excel file, the 1st 150 rows (beside the column heading) is empty, but the 150 rows is inserted after row 150. So I got 300 rows with the 1st 150 rows empty and row # 151 to Row 300 has now the rows I needed.

How could that be?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

When examing the excel file, the 1st 150 rows (beside the column heading) is empty, but the 150 rows is inserted after row 150. So I got 300 rows with the 1st 150 rows empty and row # 151 to Row 300 has now the rows I needed.

Excel "remembers" the last cell used on any of its sheets. In Excell this can be found using VBA:

Selection.SpecialCells(xlCellTypeLastCell).Select

To test for yourself BEFORE writing the new data to the worksheet, select Edit from the menu in the submenu select "Go to", in that window select Special, in the next window select last cell.

Quickest and easy proceedure is to do what Lester recommended.

Lester
you can try creating an excel file that will stand as your template save it to a location then just copy it to the folder where you will need to insert the data. You will not need to clean the data in the excel file all ou have to do is to overwrite the file with a new one.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Hollyz SSCommitted Points: 1763 More actions · Answer 5

I thought that the Drop table would let you write the excel from the top not from where the last cell it has which seems very much like the latter part.

Lester's method did work on some of my projects but mysterisouly didnot work on other projects.

His method did save me some headaches nevertheless.

Thanks, you all.