here is the scenario,

1. my OLAP, OLTP and AS server is one and the same.
2. server is sql 2000 standard edition.
3. flow : OLTP -> Staging -> OLAP.
4. all three databases are different.
5. In staging there are,
a. Fact tables
b. dimension tables
c. temp tables for each dimension and fact. (*)
6. DTS flow is like this,
OLTP -> staging
a->truncate temp tables
b->data comes to temp table[new data-flag 1, updated-flag 0]
c->dim data updated - from temp table
d->dim data inserted - from temp table
e->temp fact table[new data-flag 1, updated-flag 0]
f->if ne of the rows in fact is updated in temp, delete it from fact
g->insert all rows from temp to fact.

*** a to g is done using stored procedures.
seperate sps for each table, caled with one main proc and that proc is used
in DTS task.