Using SSIS to check if a record exists and perform a task

Question

Just to introduce what I need to do is the following:

• Input data from a stored procedure into an OLE DB data source within a data flow task
• Use the dataset output from the OLE DB source to loop through it and check if each record exists in the destination database
  • If it does, ok, go ahead.
  • If it doesn't, I want to insert a value into a seperate table.
• Then drop all records from the destination table.
• Re-populate the destination table with the entire dataset returned in the OLE DB Source.

So far I have the OLE DB source reading in the result set returned from the stored procedure, then the OLE DB Command drops all records in the destination table and lastly the OLE DB Destination table is populated with the records output from the OLE DB Source.

WHAT I NEED HELP WITH:

Can someone please tell me how to do the following: - Check every record in the OLE DB Source output to see if it exists in the destination table. - If a record doesn't exist, input data into a seperate table.

It would be much appreciated if someone could help!

Thanks, Cian

Answer 1

You can use the Lookup Task for this - pass the rows from the OLEDB source into this, and match on the keys for the rows. Send the non-matching rows to the other table.

Sounds like you want to use a Multicast before you do this though - one path to do the drop & re-insert and the other path to do what I said above (send a value to the other table).