dts inserting a new row for each change 1

[mirage10]

SQL SERVER 2000
pervasive sql v8

I created a DTS package to get import data from pervasive sql 8 into sql server 2000. this package is scheduled to run every minute.
and every minute the data is fetched.. it appends to already existing data.. that is making the table double each minute..

can some one advise what to do..??
I just want the latest copy of the data on the database..
any advise will be appreciated.

thanks

------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[DotNetGnat]

i would suggest you tyo truncate the data before you do the next import...something like this...steps in your DTS package

1.Drop any indexes(if you have any)
2.Truncate mytable
3.Tansform Data Task from
4.Create indexes

-DNG

 

[mirage10]

i really am not sure what all are the indexes the table is having..
is it not sufficient if i just truncate the table and not drop the indexes?? if this step is not taken care of.. what will be the affects??

------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[DotNetGnat]

if i am not wrong no side effects...but dropping indexes prior to truncating table will give better performance...

-DNG

 

[mirage10]

DNG,
thanks for the prompt replies.

In my DTS package I have steps

1) truncate table MIITEM
2) will build the data from a query (used query builder to get the data from other database)


but now when i query the table MIITEM from sqlserver i see no data...

in job history i see job successful..

can you say what I am doing wrong here??

thanks again for the advise


------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[DotNetGnat]

ok...before doing the step 1, we already have the talbe named MIITEM in the database....

after doing step 1...all the rows/i mean all the data is deleted from the table...

now the step 2 will insert new data...

i dont see anything wrong with it...unless there is something wrong with your query...can you show your query?? or tell more about it...
-DNG

 

[mirage10]

yes that is right.

*using a dts package i first imported table MIITEM into the present sql server database.
(this dts package was not scheduled , just ran once.. to get the table.)

.. query is as simple as "SELECT * FROM ADH.MIITEM"

i am using dts import wizard to get the data.

let me tell you the steps i followed .. may be you can figure out if i did something wrong:

1. First i already have MIITEM table in sql server (got the table using above *) pacakge name miitem3

2. created a dts package which uses the query
"SELECT * FROM ADH.MIITEM"
-used dts import wizard. (when i ran the wizard chose the option "append to existing table")

3. In management - sql server agent -jobs:

for miitem3 package which gets the data using the query above(2)
I inserted a new step -step1 as truncate table miitem.
step 2 is dts run

Pl. Note: when I donot include this step truncate table it is getting the data succesfully.. but the problem is it appends data to the table.. (thats the reason i posted the message here.. the table size doubles by the minute..)

Pl. advise .






------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[DotNetGnat]

i am little bit confused with your explanation..but here is what i would do..


dts package name: miitem3
step 1. Truncate Table MIITEM
>>>>>ON SUCCESS<<<<<<
step 2. Load Table MIITEM

thats it...so when ever i run the package miitem3...first the table is truncated...and on success i reload the table

just do this simple test on any table and see if you are getting fresh data each time or is it being just getting appended resulting duplicates...

-DNG