SSIS 数据流更新
我正在尝试找出处理以下场景的最佳方法。
概述
付款在一天中收集并存储在表 (SQL Server) 中。每天结束时，这些付款将被导出并插入到另一个数据库 (Oracle) 中。成功插入 Oracle 数据库的付款将使用时间戳进行更新。
流程
步骤 1 - 每条付款记录都必须与 Oracle 数据库进行匹配，以确保付款记录引用的值存在于 Oracle 数据库中。满足此步骤的付款将继续进行。
第 2 步 - 付款被插入到 Oracle 数据库中，任何插入错误都会被重定向到记录集。
方法
我使用 OLEDB 数据源作为付款 (SQL Server) 和查找值 (Oracle)，然后使用合并联接（左外）将数据附加到付款。使用合并连接结果的条件分割，我将与查找数据匹配的付款传递到 OLEDB 目标。
我需要更新原始付款表，仅包含插入到插入中的付款。使用条件拆分，我可以确定哪些行在步骤 1 中不匹配，并且在最终插入时重定向错误输出上的行，我可以确定哪些行失败。但是，我没有看到确定已成功插入的付款的最佳方法以及更新原始表的最佳方法。
I am trying to figure out the best way to approach the following scenario.
Overview
Payments are collected over the course of the day and stored in a table (SQL Server). At the conclusion of each day, these payments are to be exported and inserted into another database (Oracle). The payments that were successfully inserted into the Oracle database are to be updated with a time stamp.
Process
Step 1 - Each payment record has to be matched against the Oracle database to ensure that a value referred to by the payment record exists in the Oracle database. Payments that satisfy this step move down the pipeline.
Step 2 - Payments are inserted into the Oracle database, with any insert errors being redirected to a recordset.
Approach
I am using an OLEDB Data Source for the payments (SQL Server) and for the lookup values (Oracle) and then using a Merge Join (Left Outer) to append the data to the payments. Using a conditional split from the results of the Merge Join, I pass the payments that matched the lookup data down into the OLEDB Destination.
I need to update the original payments table with only the payments that made it down to the insert. Using the Conditional Split, I can determine which rows were not matched in Step 1, and redirecting rows on the error output on the final insert I can determine which rows failed there. However, I am not seeing the best way to determine the payments that were successfully inserted and the best way to update the original table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我可能会将成功付款记录的 ID 列表插入原始数据库中的临时表（使用多播 + OLE DB 目标），然后运行存储过程或使用 SQL 查询任务根据值更新原始表在临时表中。然后，您可以在完成后截断或删除临时表。
I would probably insert a list of ids for successful payment records into a temp table in the original db (using a multicast + OLE DB Destination) and then run a stored procedure or use an SQL Query task to update the original table based on the values in the temp table. Then you can truncate or drop the temp table when you're done.