SQL SSIS, insert only new rows.

SSIS workflow is really amazing when you have to add some automatism in your data flow processes. But sometimes is better to make same checks before start with a really dangerous BULK operation.

One of the most noise stuff I have to do is to launch insert of new records when I receive updates from web (web services, XML data). But when I do this I’m always afraid about duplicated rows.

Ho to check if a row exist, in SSIS?

SSIS Package contains a component called lookup. Very intuitive name. So if you need to insert only some new rows in a table you can build a workflow like this:

image

So we have three components:

  1. A OLEDB Datasource that reads data from the source table
  2. A LookUp component that check every row, if exists in the destination table. If doesn’t it redirects the rows in the error event.
  3. A Destination SQL that receive only the new rows, the rows that are in error in the lookup component.

Easy and automatic.