Need some help with DTS

I need to set up an SQL datbase table to automaticaly update its data from another database (outside of the SQL server). I am not extreemly familiar with SQL server and DTS, so im not sure how to go about this.

What i need to do is;

1. select all records in the destination database that no longer have matches in the source database and set a column in the destination databvase (one not in the source table) to a value, such as "not active". So if the record is deleted in the source database, its only marked as deleted in the destination database.

2. Update columns in the destination database with changes made to the source.

3. Add any new records to the destination database that have been added to the source database.

4. Have this run once a day.

I have done little research and the parts 2 and 3 dont seem like it would be hard to figure out, but the first part I cant seem to find anything on how do get it accomplished.

I am working in SQL Server 7. Any help for this noob to DTS would be appreciate, especialy articles on how to accomplish set 1.Sounds like a case for replication database but don't know if it works on linked database.
Think you can do it with Views, maybe called by DTS

First link source database so you can use it's Alias in views (SourceServer)
Do this in Security\linked servers in SQL Server Enterprice Manager

Make VIEW1 in destination dbase:

SELECT DestinationTable.Record_Id
FROM DestinationTable LEFT JOIN SourceServer.SourceDatabase.Dbo.SourceTable AS ST ON DestinationTable.Record_Id = ST.Record_Id
WHERE (((ST.Record_Id ) Is Null));

Make VIEW2 that uses results from VIEW1:

UPDATE DestinationTable INNER JOIN VIEW1 ON DestinationTable.Record_Id = VIEW1.Record_Id SET DestinationTable.RemarkColumn = "deleted"|||Tried making that View1 lke you said, but its returning all rows in the destination table, not jus the ones that dont have matches in the source table|||Make sure the join is LEFT JOIN. Because of criteria in source (IS NULL) it should only return the ones you want.

Use CAPITAL (IS NULL) in criteria

good luck|||I got that working, thanks.

New problem. SQL server will not let me save a view with an update command in it.

I was thinking i could just use the SQL statement and have it to run once a day. Is there a way to schedule SQL statements to run peridicaly in DTS?sql