September 25, 2009 at 9:57 am
I think this is the most basic requirement and I need help doing it.
I have a flat file table as
SourceTable (Fname, Lname, phone, address)
in sql i have
DestTable (contactid, Fname, Lname, phone, address)
here i have contactid as int identity(1,1) for primary key.
I will run the package monthly and i want to achieve
Insert new row
avoid duplicates
update changed rows
thank you for your feedback
September 25, 2009 at 5:58 pm
I assume a row coming in is a duplicate if it matches on Fname, Lname, phone, and address.
How do you determine that an incoming row should cause an update to a row in the table? In other words, what combination of Fname, Lname, phone, and address is the natural key to DestTable?
September 25, 2009 at 6:03 pm
the combination of Fname and phone is the primary key to the table
September 25, 2009 at 9:39 pm
If you are certain SourceTable does not have duplicate keys (Fname and phone), you can do the following:
In the data flow task, add a flat file source for SourceTable and send its rows to a lookup transformation.
Set the lookup to redirect rows on failure (or no match if you are using 2008).
Connect it to DestTable on Fname and phone.
The redirected rows can be sent to an OLE DB destination for DestTable so that they will be inserted.
The matched rows can go to a conditional split where rows with changed Lname and/or address will end up going to an OLE DB command to update Fname and address for the contactid returned by the lookup.
UPDATE dbo.DestTable
SET Lname = ?-- ? is Lname from SourceTable
,address = ?-- ? is address from SourceTable
WHERE contactid = ?-- ? is contactid from lookup
The unchanged rows from the conditional split will be ignored.
--------------
The situation becomes more complicated in the case of possible duplicates in SourceTable, especially if they have a key that is not already in DestTable. Each will be inserted to DestTable.
If the key is already in DestTable then each could cause an update but the conditional split will be comparing to the values in DestTable at the start of the run.
In this case, you might want to preprocess SourceTable. It might help to dump it into a work database table.
----------------
Other things to watch out for are leading and/or trailing spaces and differences in case. The lookup is case sensitive and checks spaces.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply