I have a staging table and the production table. I use a conditional split after a merge join (left outer) to determine if the row exists in the destination (or if it is a new row)
if existing row then I am using the OLE command to update the production table row by row 🙁
in a sproc you can do an update like the following:
set p.f3 = s.f3, p.f4 = s.f4 ...
from Staging s
left join Production p
on s.f1 = p.f1 and s.f2 = p.f2
where p.f1 is not null
this would update all specified fields in production table with values from staging table.
a similar query can be used to insert new rows by specifying where p.f1 is null
I am wondering if there is a ssis component that can accomplish this same type of update. I'm guessing this is stored procedure territory....