如何通过使用SSIS将行从MySQL插入/更新到SQL Server [英] How to insert/update rows from MySQL to SQL Server by using SSIS

问题描述

我正在寻找从MySQL连接向SQL Server连接插入或更新行的最佳实践。

I'm looking for the best practice to insert or update rows from a MySQL connection to a SQL Server connection.

首先，我添加了ADO.NET数据源MySQL内容（一个简单的表Supplier，具有两个字段ID和名称）。然后，我添加了一个Lookup转换以拆分新行/更新行。当我需要插入新行时，它运行良好。但是，我想使用Command OLE DB更新现有行，但是由于我的连接管理器和组件之间的不兼容（ADO.NET与OLE DB），所以它不起作用。

First of all, I added a ADO.NET data source to grab MySQL content (a simple table Supplier with two fields id and name). Then, I added a Lookup transformation to split new rows / updated rows. It works well when I need to insert new rows. However, I would like to use a Command OLE DB to update existing rows but It doesn't work due to a incompatibility between my connection manager and the component (ADO.NET vs OLE DB).

是否有任何更新已修改行的想法？！我应该使用缓存组件吗？！

Any idea to update modified rows ?! Should I use a cache component ?!

预先感谢！

推荐答案

<只需摆脱查找和条件拆分。

Just get rid of the lookup and conditional split all together.

在SSIS包之外，构建一个临时表，其中包含插入/更新所需的字段。

Outside of your SSIS package, build a staging table that contains the fields you need for inserts/updates.

在您的SSIS程序包中，创建一个执行以下操作的控制流：

In your SSIS Package, create a control flow that does the following:

• 执行SQL任务以截断暂存表。


• 数据流任务，用于将MySQL数据从源系统加载到登台表。如果您可以基于仅更改类型的过程（例如使用检查的时间戳）来执行此操作，则速度会更快。


• 执行SQL任务以在其中执行UPDATE语句


• 执行SQL任务，以基于目标表和登台表的查询（在目标表上使用INSERT语句）对目标表执行INSERT语句。一个不存在的地方或某个关键领域的地方）

• Execute SQL Task to truncate the staging table.
• Data Flow task to load the MySQL data from the source system to the staging table. If you can do this based on a "changes-only" type process, such as using a timestamp that you check, it would be faster.
• Execute SQL Task to perform an UPDATE statement on your target table using the staging table joined to the target table.
• Execute SQL Task to perform an INSERT statement on your target table using a query based on the target table and your staging table (with a WHERE NOT EXISTS or some such on a key fied)

这篇关于如何通过使用SSIS将行从MySQL插入/更新到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！