SSIS to copy data from one table to another, where not in destination table

Question

I'm in the process of creating an SSIS package on a server (server1) that looks at the data in a sql db on another site (server2) and copies relevant rows across.

The SQL statement required is:

SELECT *
FROM server2.ordersTable
WHERE
OrderID Not In (SELECT OrderID FROM server1.ordersTable

This selects data from server1 which isn't in the table on server2 (based on order id) I then need to insert the result into a table on server1

How would I approach this? What components do I need etc...?

Answer 1

you can create a linked server for one of the servers first say LinkedServer1 for server1 and then you can use OPENQUERY like:

SELECT * FROM server2.OrdersTable WHERE OrderID NOT IN ( SELECT OrderID FROM OPENQUERY(LinkedServer, 'SELECT OrderID FROM LinkedServer1.OrdersTable) )

Answer 2

Presuming you can use OPENQUERY or a direct linked-server join on server2 to server1 (as in your example or the answer from @schrodinger's code), that would be the best solution to minimize the data over the wire.

But if that isn't available, you have two other options:

1) You can use the Lookup data-flow transformation in SSIS to check the record against the existing OrderIDs and only push the new records using the conditional split transformation.

2) You can transfer the entire table from Server2 into a temporary table on Server1, then compare on Server1 using a variant of the code you posted.