sql server - How do I handle rows that were deleted from the source using SSIS Slowly Changing Dimension -

i trying implement etl process our type 1 changing dimension tables in sql 2014 database. load needs happen across servers, , prefer not use linked servers.

i have been looking ways in ssis , found changing dimension wizard works fine except seems allow either inserting new rows or updating rows there match on business key, haven't found place allows me handle when record exists in dimension table deleted source. make sure these deleted. missing something? has found better way handle in ssis?

i know dump table on destination server , write tsql merge, there seems should simple way in ssis.

first, avoid scd functionality in ssis, performance tends terrible - i've been told avoid ms certified trainers, plenty of people lot of experience. it's ok-ish on small dimensions, tends become unmanageable. there's blog post here thinks it's usable in situations, suggest using staging table updates.

if want in ssis use lookup find rows need deleted (find rows in destination aren't in source using no match output), ole db command delete them. i'd give serious thought moving data on staging area , doing in tsql, because ssis row agonising row. scd tool - might ok on small amounts of data, if you're dealing larger amounts (or might in future), may become unmanageable.

if don't want move of data on staging area, use ssis build table holding unique ids of rows need deleting, fire off execute sql task control flow delete them @ once.