SSIS - 传输后更新源记录 [英] SSIS - Update source records after transfer

问题描述

我有 2 个 SQL Server.服务器A和服务器B

I have 2 SQL Servers. ServerA and ServerB

他们都有一个名为OrderRequest"的表

They both have a table called "OrderRequest"

ServerA 位于内部"，ServerB 位于我们的数据中心.

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

ServerA 有一个链接服务器 - ServerB在 ServerB 上有一个链接服务器回到 ServerA

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

我需要删除后一个链接服务器，因为允许 ServerB看到"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.

我们有一个 SSIS 包，可以将数据从我们的数据中心 (ServerB) 复制到我们的内部服务器 (ServerA)

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

目前，SQL 语句是这样的:

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

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

如您所见，这将要求 ServerB 能够看到"ServerA

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

我想做的是:

SELECT *
FROM ServerB.OrderRequest
WHERE
Transferred = 0

这很容易做到.但是，在我的 SSIS 中，我有一个联合(因为我有多个 WebDB)之后，它们被插入到 ServerA 中.

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.

我需要做的是在成功插入时将 Transferred 设置为 true.我该怎么做?

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

推荐答案

显然有很多方法可以做到这一点，但这取决于几个因素(例如，您在进行传输时是否在 ServerB 中插入了更多记录??)

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?)

• 对 foreach 容器进行多播里面有一个 OLE DB 命令.称呼更新 ServerB 上的每条记录.
• 在您选择之前开始交易.选择完成后，将所有行更新为转移(如果您想要更多的原子性，请选择临时表以确保正确更新).完成后:提交.失败:回滚.
• 只需在完成时运行 SQL 命令:UPDATE ServerB.OrderRequest SET Transferred = 1 WHERE Transferred = 0

这篇关于SSIS - 传输后更新源记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！