Integration Service: Best Way to Compare Two Tables to Retreive Missing Keys For Use In Later Decisions

Question

I am currently trying to develop a SSIS Package and am lost because I am a no0b. I have two tables, one is updated and one needs to be updated. I need to compare these two tables and find the primary keys that have been added to the first table and that are not present in the second table.

I need these primary keys in the next set of queries which would use these to determine what is returned (WHERE). Is this possible with SSIS? If so, which toolbox items should i be concentrated on?

Thanks in advance for your help.

Answer 1

This is a common task and I would do the following:

(1) Within a Data Flow Component drag over your OLE DB Source (probably already have that).

(2) Connect the source component with a Lookup Component. Within that lookup choose the other table in which you wish to compare to and in the column tabs (on the left) match up the primary key columns and check any column you'd like to be returned. Lastly, go into the Error Handling section within that same Lookup component (on the left as well) and choose Redirect on Failure.

(3) Now when you choose your next component (whatever that may be) you will pick the red flow connector arrow and that will be the nonmatches.

Make sense? Likewise, you can still use the matched flow by connecting the green flow arrow to yet another component. Hope this helps.