sql - Transform and copy table data -

sorry if questioned asked before, posting it, since not able find related in forum. appreciated. here scenario.

we have sql table view (just read access) set of data located on server on domain a. have our sql database table b located on server b on domain b. have got port opened between 2 domains.

we need write ssis job (runs per day) data view , insert in table b. best way achieving this.

note:

• table view on server doesn't have deleted or last modified columns wont able figure out changes. (if there other way please let me know)
• database data around 50k records

approach 1:

• export data view
• transform it
• drop table on server b
• insert transformed data

approach 2:

• export data view
• transform it
• insert temp table
• make log entry log table
• when log entry says copied, trigger delete rows , copy data temp table table b.
• if went wrong, rollback.

i recommend third approach applying new staging table , history table in database b on server b. staging table mirror table b, not contain constraints , have additional bit column defining status. history table mirror table b structure contain 2 additional columns (changedate , changemade). lastly, approach need identify column(s) used define records view unique.

• truncate staging table (from previous results).
• export data view (server a) staging table (server b).
• run sql task check consistency , quality of each record. records pass, set status bit field value 1, otherwise set 0.
• apply merge call in sql task both transform data source (staging table) target table (table b) , update history table. applied records have status = 1. merge can output history of changed new history table indicating "i" insert, "u" update or "d" delete based on matching defined in merge.
• for records in staging table value of 0, send email whoever needs know x number of problem records found.

the idea here process doesn't come halt , not need force rollback if single bad record found. also, can monitor daily process viewing staging table. i've taken approach in past , integrated sending email alerts links ssrs reports reporting on problem records. doing allowed me proactively find patterns in problem records , work upstream of me resolve problems. if have on million records being pulled view, may want add staging table surrogate key (set primary) identity starting @ 1 , automatically increments 1 each new record imported view. before running merge, set clustered index using surrogate key. improve performance of merge. before truncating staging table in first step, drop index.

hope helps.