SSIS - Update source records after transfer

Question

I have 2 SQL Servers. ServerA and ServerB

They both have a table called "OrderRequest"

ServerA is "in house" and ServerB is in our datacenter.

ServerA has a linked server - ServerB On ServerB there is a linked server back to ServerA

I need to remove the latter linked server, as the firewall that allows ServerB to "see" ServerA is changing, and won't allow this any more.

We have a SSIS package that copies data from our datacenter (ServerB) to our in-house Server (ServerA)

At the moment, the SQL statement is along the lines of:

SELECT *
FROM ServerB.OrderRequest
WHERE
OrderID NOT In (SELECT OrderID FROM ServerA.OrderRequest)

As you can see, this will require ServerB to be able to “see” ServerA

What I want to do is something like:

SELECT *
FROM ServerB.OrderRequest
WHERE
Transferred = 0

This is easy enough to do. However, in my SSIS I have a Union (as I have more than one WebDB) After that, they are inserted into ServerA.

What I’d need to do, is set Transferred to true, on successful insert. How would I go about doing this?

Answer 1

There are obviously many ways to do this, but it depends on a few factors (for instance, are you inserting more records into ServerB as you are doing the transfer?)

• Do a multicast to a foreach container with an OLE DB command inside. Call update on each record on ServerB.
• Start a transaction before you select. After the select is complete, update all rows to transferred (if you want even more atomicity, select into a temp table to ensure you're updating correctly). Once complete: commit. Fail: rollback.
• Simply run a SQL Command on completion: UPDATE ServerB.OrderRequest SET Transferred = 1 WHERE Transferred = 0