插入时更新
请先阅读更新1！这是 SSIS 特定问题。
我有以下任务：
- 我需要定期将数据从 MySQL 中的表 A 移动到 MsSQL 中的表 B。
- 然后，需要在表 A 中更新所有移动的行（更改单个列值）。
我通过创建以下数据流完成了任务 1：ADO NET Source ->数据转换-> SQL Server 目标。效果很好。我运行这个查询 X 分钟。
现在，使用 SSIS 工具，如何更新我刚刚在 MySQL 中“数据流”的行？如果我要使用普通 SQL，我会这样做（在 MySql 中）：
更新表 mytable set status="moved" WHERE ...
（这将确保下次任务 1 提取数据 - 它会跳过已经“移动”的所有行）
所以问题是我不知道如何将第二个任务中的 WHERE 子句与第一个任务的结果集连接起来。
Update1：我对优化更新过程不太感兴趣。我在这里对其进行了简化，以强调以下问题：如何在 SSIS 中实现此功能。我特别感兴趣的是 SSIS 中的哪种数据/控制流块我需要使用什么序列。
Please read Update1 first! This is SSIS specific question.
I have the following tasks:
- I need to periodically move data from table A in MySQL to Table B in MsSQL.
- Then all of the moved rows needs to be updated in Table A (change a single column value).
I have accomplished task 1 by creating the following Data Flow: ADO NET Source -> Data Conversion -> SQL Server Destination. It's working great. I run this query X minutes.
Now, using SSIS tool, how do I update the rows that I just "Data Flow"'ed in MySQL? If I was to use a plain SQL I'd do (in MySql):
update table mytable set status="moved" WHERE ...
(this will make sure that next time task 1 pulls the data out - it skips all rows that were already "moved")
So the problem is that I don't know how to connect WHERE clause in the 2nd task with the resultset of the 1st task.
Update1: I'm not so interested in optimizing the update procedure. I have simplified it here to put emphasis on the the question: How to implement this in SSIS. I'm specifically interested in what kind of Data/Control Flow blocks in SSIS I need to use what is the sequence.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我发现最简单的方法是拥有一个中间状态，例如：
status = 1在源标记行，UPDATE myTable SET status = 2 WHERE status = 1更新后：
您可以在目的地之前使用多播，并捕获传输到另一个表
CapturedRows在源数据库中。之后，使用执行 SQL 任务更新源表中的行，例如：在此之后，您将使用另一个执行 SQL 任务来截断
CapturedRows桌子。您还可以直接将OLE DB Command 连接到多播，以便在记录流动时逐一更新记录——但这会非常慢。
要使用此功能，您必须为数据流任务设置失败时失败包，以确保它在插入失败时停止，并且事务选项对于需要的包和对于支持的数据流。
I find that the easiest is to have an intermediate status like:
status = 1UPDATE myTable SET status = 2 WHERE status = 1AFTER YOUR UPDATE:
You could use Multicast just before your destination and capture IDs of records transferred over into another table
CapturedRowsin the source DB. After that use Execute SQL Task to update rows in a source table, like:After this you would use another Execute SQL Task to truncate the
CapturedRowstable.You could also connect OLE DB Command to the Multicast directly, to update records one by one, as they flow -- but that would be quite slow.
To use this, you would have to set Fail Package On Failure for the Data Flow Task to make sure that it stops if an insert fails and Transaction Option for package to required and for the Data Flow to supported.
执行此类操作的一种方法是：
将要移动的数据复制到源系统上的临时表
移动数据像现在一样从临时表（源）到目标系统
像现在
更新 ID 所在的主源表临时表
删除临时表
One way to do this type of thing:
Copy the data to be moved to a temp table on the source system
Move the data from the temp table (source) to your target system like you do now
Update the master source table where the Ids are in the temp table
drop the temp table
为 table-a 定义一个更新触发器，当 table-a.status 更改为“moved”时，该触发器将记录插入到 table-b 中。
define an on-update trigger for table-a that inserts the record into table-b when table-a.status is being changed to "moved".