如何更新 SSIS 中的表? [英] How do I update a table in SSIS?

问题描述

我在数据库 1 中有一个表，其中包含 x 和 y 列.我在数据库 2 中有另一个表，其中包含 x 和 y 列.我想将数据库 1 中的所有 y 列更新为数据库 2 中的 y 列，其中数据库 1 中的 x 列与数据库 2 中的 x 列匹配.

I have a table in database 1 with columns x and y. I have another table in database 2 with columns x and y. I want to update all the y columns in database 1 to the y columns in database 2 where the x columns in database 1 match the x columns in database 2.

这似乎是一项令人难以置信的微不足道的任务，但我不知道如何在 SSIS 中做到这一点.我的数据流任务中有一个 OLE DB 源和目标，并且映射了 2 列，但它一直尝试插入而不是更新，它失败了，因为目标中有一堆其他不可为空的列我没有映射.

This seems like an unbelievably trivial task, but I can't figure out how to do it in SSIS. I have an OLE DB Source and Destination in my data flow task and I have the 2 columns mapped, but it keeps trying to insert instead of update, and it fails because there are a bunch of other non-nullable columns in the destination that I don't have mapped.

推荐答案

使用 SSIS 做数据转换的问题是源数据集和目标数据集都需要拉到 ETL 服务器上的内存中，转换需要在那里发生，然后结果必须写回目标服务器.

The problem with using SSIS to do data transformation is that both the source and target data sets need to be pulled up into memory on the ETL server, the transformation needs to happen there, and then the results have to be written back down to the destination server.

这是网络密集型.它是内存密集型的.它只是不太理想.这也是为什么你很难弄清楚的原因.在服务器上，它只是一个 UPDATE 语句，但是将它放入 SSIS 需要更多的步骤，而且没有第三方工具，除了行之外没有开箱即用的方法来做任何事情按行更新.

It's network intensive. It's memory intensive. It's just less than ideal. That's also why you're having trouble figuring it out. On a server, it's just an UPDATE statement, but getting it up into SSIS requires many more steps than just that, and absent third party tools, there's no out of the box method to do anything other than row by row updates.

在您的情况下，您的源数据相对较轻，我建议最有效的方法是使用 SSIS 将源数据从源服务器移动到目标服务器并将其放入工作/保留/中间表.SSIS 在将数据从 A 点移动到 B 点方面绝对很棒.然后，在 Data Flow 之后，使用 Execute SQL 任务来调用 UPDATE 存储过程，或者继续在包中编写 UPDATE 语句.

In your situation, where your source data is comparatively lightweight, I would suggest that the most efficient approach would be to use SSIS to move the source data from the source server to the target server and drop it into a working/holding/intermediate table. SSIS is absolutely awesome at moving data from point A to point B. Then, after the Data Flow, use an Execute SQL task to either call an UPDATE stored procedure, or go ahead and write the UPDATE statement in the package.

这样做可以将 DML 从 ETL 服务器卸载到 SQL Server，而 SQL Server 正是为这种工作而设计的.如果你愿意的话，有点像让每个人做他们擅长的事情"的方法.

Doing it that way off-loads the DML from the ETL server to the SQL Server, which is designed for exactly that kind of work. Sort of a "let everybody do what they're good at" approach, if you will.

这篇关于如何更新 SSIS 中的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！