Hello Rob,

I assume that you cannot use or don't want to use Transactional Replication or Change Data Capture against your Source System database(s)? (Note: CDC is only available from SQL Server 2008 onward and then only in Developer and Enterprise Editions).

If you are doing everything with SSIS packages then could write a package (or series of packages) that start with your Website database and look up data in your Source System database(s). any non-matching rows indicate that a row that exists in the Website database no longer exists in the Source System database and can therefore be marked for deletion/deactivation or deleted.

Clearly, the more data that you have, the longer a process like this is going to take so you may want to minimise the amount of data that you are working with by only selecting from both databases (Source System and Website) the column(s) that uniquely identify a row. An alternative is to compute and store a Hash value for a row or set of columns and compare between source and target database.....of course you may be unable/unwilling to modify the database structure.

You mentioned wanting to avoid truncate/reload which I wholeheartedly agree with; I would also advise doing your updates and deletes in a set-based operation rather than using the OLEDB Command Task in SSIS.

Here are a couple of good articles about incremental data loading:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62063

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69766

Regards

Lempster