June 6, 2005 at 12:54 am
I am using a very simple and straight forward DTS, where my source is a SQL query and my destination is 'Sheet1' of my Excel file to which the data is exported.
The first time I run the DTS, it work just fine and inserts the output of my query. (say 100 rows). The next time I run the query , it "APPENDS" the output of my query into the excel file. Therefore my excel file now contains 200 rows, for the same query.
How can I ensure that with each excution of the DTS , it does a Fresh insert and not appends data into the excel sheet.
The whole process is automated and shouldn't require manual intervention.
Thank you
June 6, 2005 at 3:32 am
For a fullproof method, do the following.
Add a Execute SQL Task and name it drop table. Point the connection to the excel file - NOT the db connection. Code the sql as:
drop table yourtable
Add another Execute SQL Task pointing it again to the spreadsheet connection and name it create table. Go into the transform data task between the db connection and the excel spreadsheet and go to the destination tab and click create. The sql generated is what you want to put into the create table sql statement.
Link all of the objects together in the package in the following order.
Drop table - On success goto
Create table - On success go to
Db connection Transform data task to spreadsheet.
That's all there is to it.
June 6, 2005 at 6:11 am
It worked!!! Brilliant..Thank you so, so very much.
June 7, 2005 at 7:04 am
It will work, but other thing you have to know is as you exectue dts ( lets say once a day as schedule in production) to insert into excel file. the excel file size will grow. ( I am not sure why). and after six month or so the file size is unmanagable even though you insert only few rows a day(lets say 10 rows a day to give you an example). to over come this problem i created a template excel file that is mirror image of output excel file. and very first step is in my dts is to delete the output file and copy it from the template.
June 7, 2005 at 7:25 am
Thank you for the valuable tip. Yes, you are right, I did notice the size increasing, but wasn't sure why. I'll incorporate your suggestion and hope it works fine.
Thank you once again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply