I am a newbie in SQL 2000 dts. I need to create a dts package to update the
data warehouse currently in the Sql server. There are two tables, DW1 and
DW2 in the data warehouse. The primary key is DW1ID and DW2ID and DW1ID is
a secondary key for the table DW2. The data warehouse consists of data from
several Sql data sources and those data sources are updated in different
time (I am talkng about months apart). I need to automate the update process
to the data warehouse when the sql data source is updated. The ideal process
should inclued but not limited to the following process:

1) Back up the data warehouse database
2) Delete records belong to the Sql data source from the DW1 (Cascade delete
is set up in the DW2)
3) Append records from Sql data source to DW1 and DW2
4) Notify someone when the process is completed (success or failure)

As for item 3, I need help to create a Sql loop on each record append to DW1
as the newly created DW1ID is used as a secondary key in DW2. Then I can
create a dts package for each of the Sql data source.

Thanks