Not Exists in SSIS

There are many times that I would like to insert a record into a table only if it doesn't exist. This is done easily in T-SQL:

insert TableA
select B.RepID
from TableBB
where not exists(select 1
from TableAA
where A.RepID=B.RepID)

SSIS is a little more complex, but once you get the hang of it, it's very powerful.

Create adataflowobject and within it place adataflowsource. Select from theSSIStoolbox, underdataflowtransformations, theLookuptask. Attach your task to thedataflowsource. Once you have that connected we move on to the next part.

Double-click on theLookuptask and it will open up a window with 3 tabs. First tab is the reference table, that is the table you will be checking. In the example above, that would beTableA. I personally like to select only the columns I need to validate what I am inserting (which would most likely be the 1 or more columns that make up the unique key). Once this is done, click on theColumnstab.

TheColumns is where you link the reference table, (Table B) to the source table (TableA in the above example). Drag one or more columns from the Available Input Colums on the left to the corresponding Available Lookup Columns on the right. Click any one of the check boxes from the right side. It doesn't matter which one as we are only using this as a flag. Once you do this, you will see an entry in the grid below with Lookup Column, Lookup Operation, Output Alias. This should be filled in with the column on the left, the corresponding column you clicked the check box on to the right and in the middle it should say <add as new column>. Remember this column name. We are going to skip the Advanced tab (I'll cover that in another post soon). Now, at the very bottom left of the Lookup task you will see a button Configure Error Output, click on it.

Across the top you will see 5 columns, you want to focus on the third Error. Right below that column you will see, by default,Fail Component and if you click in it you will have 3 options: Ignore Failure, Redirect Row, Fail Component. You want to select Ignore Failure. You might be asking why.

The purpose of the Lookup task is to find an existing record and if no record matches it is considered a failure. We alter that default behavior and tell SSIS to pass the record through even if there is no match. We catch the failure with a Conditional Split task.

The output of the Lookup task goes into the Conditional Split. Drag down the ISNull() function and place it on the first line. Click on the Columns folder on the left side and find the column we selected up above in the Lookup tab. Drag that column into the ISNull function. I usually change the output name to something along the lines of Valid_Record or New_Record. What you have done here is basically tell the Conditional Split task to output any record with that has a null value, which translates into: Pass any record that doesn't already exist. You then connect the output of that condition to your next task and continue on programming. There you have it, usingSSISto input records that don't already exist.