SSIS 2008 lookup

I use a lookup componen开发者_运维问答t. When No Match output, I insert the rows into a target table. I would like to update the target table with this rows when Lookup is matched. How can I do that?

Thx!!

In the Lookup transformation, map Lookup Match Output to an OLE DB Command transformation. In the OLE DB command transformation use an UPDATE statement or a stored procedure and map the columns accordingly. Here is a link that describes how to use OLE DB Command transformation.

Please note that if you have too many rows to update, OLE DB Command transformation might slow down things.

There are couple of options:

1. You can use a second Lookup Transformation between the first Lookup transformation and the OLE DB Command. In the second lookup, map all the columns between source and destination that you will be updating including the key column and redirect the output to OLE DB Command only if there is not matching records.

2. Split the output from Lookup Match Output to multiple outputs using a sequence number and have multiple OLD DB Command transformations. Please find my answer in this Stack Overflow question where I split output from one transformation into multiple output before redirecting to an OLE DB Command.

Hope that helps.