November 8, 2015 at 2:59 pm
I’m looking for some advice on how to create an SSIS package that will do the following.
Server1.Source1
Field Name
AnID
Acoulmn
Acoulumn
Server2.Staging1
Field Name
AnID
Acoulmn
Acoulumn
Server2.Destination1
Field Name
AnID
Acoulmn
Acoulumn
The package will copy all of the data from Server1.Source1 to Server2.Staging1. It will then compare the field AnID (on against Server2.Stagintg1) against Server2.Destination1 and add any rows where the AnID does not exist . Can anyone help.
November 9, 2015 at 11:43 am
1. Execute SQL Task that truncates Server2.Staging1
2. Dataflow task that reads all the data from Server1.Source1 and loads into Server2.Staging1
3. Execute SQL task that loads Server2.Destination1 using a query like this:
Insert into Server2.Destination1
([column list])
SELECT [column list] From Server2.Staging1 as S WHERE NOT EXISTS (SELECT 1 from Server2.Destination1 as D WHERE S.AnID = D.AnID);
Ideally you'd have some way of knowing what are new rows in Server1.Source1 and only load those rows into Server2.Staging1 because step 2 above is going to get very long as the source data grows. Also if you are only adding new or modified rows to Server2.Staging1 you can add a processed date or flag to that table and eliminate step 1 and only load unprocessed rows from Server2.Staging1 to Server2.Destination1.
The other thing to consider is how to handle modified rows in Server1.Source1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2015 at 12:08 pm
If the solution you are considering is as simple as you suggest, there is little point in staging anything. Instead
1) Dataflow task with source Server1.Source1
2) Data source to fully-cached Lookup (base query "select AnId from Server2.Destination1"), match on AnId.
3) Amend the properties of the Lookup to redirect failed lookups to the no match output.
4) Send the 'no match' output to Server2.Destination1.
5) No need to do anything with the 'matched' output (until you decide that you need to update existing rows as well as inserting new ones)
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply