SSIS - Update source records after transfer
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?
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