使用SSIS更新MYSQL中的记录
我正在编写一个 SSIS 包,它具有来自 SQL Server 源的条件拆分,该源将记录拆分为更新或插入到 MYSQL 数据库中。
SQL Server 连接具有适用于 OldDB\SQL Server Native Client 10.0 的提供程序 .NET 提供程序。
MYSQL 连接是 MYSQL ODBC 5.1 ADO.NET 连接。
我正在考虑使用条件拆分的 OLE DB 命令分支来更新记录,但我连接使用它并连接到 MYSQL 数据库。
有谁知道如何完成这项任务?
I am writing an SSIS package that has a conditional split from a SQL Server source that splits records to either be updated or inserted into a MYSQL database.
The SQL Server connection has provider .NET Provider for OldDB\SQL Server Native Client 10.0.
The MYSQL connection is a MYSQL ODBC 5.1 ADO.NET connection.
I was thinking about using the OLE DB Command branching off of the conditional split to update records but I connect use this and connect to the MYSQL database.
Does anyone know how to accomplish this task?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我将写入临时表以进行更新,包括要更新的 PK 和列,然后使用该表和要更新的表执行 UPDATE SQL 语句。另一种方法是对每一行使用该命令,但根据我的经验,这似乎表现不佳 - 至少与漂亮的胖批量插入和单个更新命令相比。
就此而言,我想您可以完全不使用条件拆分,将所有内容写入临时表,然后在 SQL 中连续使用 UPDATE 和 INSERT。
I would write to a staging table for updates including the PK and columns to be updated and then execute an UPDATE SQL statement using that table and the table to be updated. The alternative is to use the command for every row and that just doesn't seem to perform that well in my experience - at least compared to a nice fat batch insert and a single update command.
For that matter, I guess you could do without the conditional split altogether, write everything to a staging table and then use an UPDATE and INSERT in SQL back to back.
也许,以下 MSDN 博客链接可能会对您有所帮助。我没试过这个。
如果我没有 OLEDB 提供程序,如何更新和删除?
该帖子建议以下三个 选项。
在发表上述文章之前,作者还发表了另外两篇关于 MySQL 的文章。
连接到SSIS 中的 MySQL
从 SSIS 写入 MySQL 数据库
希望这能为您指明正确的方向。
Probably, the following MSDN blog link might help you. I haven't tried this.
How do I UPDATE and DELETE if I don’t have an OLEDB provider?
The post suggests the following three options.
The author also had posted two other articles about MySQL prior to posting the above article.
Connecting to MySQL from SSIS
Writing to a MySQL database from SSIS
Hope that points you in the right direction.