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?

Answers


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

Need Your Help

Rendering 20 large identical listBoxes

html listbox render

I have an HTML table with rows (20 rows).

CUDA Multi-GPU Kernel Compilation

compilation cuda nvcc pycuda multi-gpu

When compiling a CUDA program that launches kernels on multiple devices, does nvcc internally compile a version of the kernel for each device?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.