How to delete rows based on comparison from Data Flow Task in an SSIS?

Question

I have a DataFlow task with two OLE DB Source objects. This is the SQL I want to achieve using SSIS:

Insert into server2.db.dbo.[table2] (...)
Select col1, col2, col3 ...
from Server1.db.dbo.[table1] where [table1.col1] not in 
(Select col5 from server2.db.dbo.[table2] Where ...)

I am pretty new to SSIS and not sure how to achieve this. I thought I could do this using the Data Flow task and populating the first source with the data from server1.db.dbo.table1 and the second source with server2.db.dbo.[table2] and then do the conditional check before inserting it into server2.db.dbo.[table2]. I am not sure how to do the conditional check though. Any help is appreciated.

Answer 1

The simplest way to do this is to keep your oledb source object from server1 with the sql select col1, col2, col3 ... from db.dbo.[table1]. Then next in your data flow place a lookup from server2 with the sql select col5 from db.dbo.[table2] where .... Set the error conditions on the lookup (button in bottom left of dialog) to redirect row on no match. Set the match conditions (tab at the top of the dialog) to match between col1 and col5 by dragging from col1 on the left to col5 on the right (an arrow should appear between them if you have done this right). When done, place your OleDb destination object and drag the error output from the lookup to the input of this object. Select server2.db.dbo.[table2] as your destination table and map your rows properly.

Answer 2

While a lookup is fine (directing rows only on no match), it is sometimes better to stage the data into a table and then do it in a SQL task on the destination server just as your SQL statement is.

If you use the lookup, you have to watch out - it will cache the entire set depending on the settings in the lookup task (you'll see the package taking a long time to initialize), and SSIS is case-sensitive, while SQL Server will depend on collation settings.