November 9, 2015 at 3:23 pm
I have created a package that will insert new rows into destination1 if the AnID does not exist in Source1. This uses a data flow task which contains a oledb source, lookup and oledb destination.
Source1 Field Name AnID Acol1 Acol2 Destination1 Field Name AnID Acol1 Acol2
I want to be able to update the destination1. Acol1 and destination1. Acol2 if the Source1.Acol1 or Source1.Acol2 have changed.
To do this, would I need to create separate data flow task that includes a source, lookup and destination. Or is this possible to build into my insert new records data flow task
November 10, 2015 at 2:51 am
system243trd (11/9/2015)
I have created a package that will insert new rows into destination1 if the AnID does not exist in Source1. This uses a data flow task which contains a oledb source, lookup and oledb destination.Source1 Field Name AnID Acol1 Acol2 Destination1 Field Name AnID Acol1 Acol2
I want to be able to update the destination1. Acol1 and destination1. Acol2 if the Source1.Acol1 or Source1.Acol2 have changed.
To do this, would I need to create separate data flow task that includes a source, lookup and destination. Or is this possible to build into my insert new records data flow task
Divert all your matched rows (ie, where AnId does exist) into a staging table (which gets truncated at the start of every package execution).
After the data flow has completed, run an ExecuteSQL task which (conditionally) performs the update:
update t
set ACol1 = s.Acol1, Acol2 = s.ACol2
from target t
join staging s
on t.AnId = s.AnId
where not exists
(select s.ACol1, s.ACol2 intersect select t.ACol1, t.ACol2)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply