I have two unlinked servers one production and one reporting. I'm using an SSIS package to incrementally update a table on the reporting server. On the reporting server I have a staging table that lists items that need to be updated or imported. Currently I have a data flow task that pulls the source table from the source server, does a look up with the staging table and then updates the destination table. My problem with this is I don't want to have to pull the entire source table everytime I run the update. Is there a way to use the staging table from reporting server to select data from the the production server? Basically I want my data source query to look something like this.
我有两个未链接的服务器，一个生产和一个报告。我正在使用SSIS包来逐步更新报表服务器上的表。在报告服务器上，我有一个临时表，列出了需要更新或导入的项目。目前，我有一个数据流任务，它从源服务器提取源表，查找登台表，然后更新目标表。我的问题是我不想每次运行更新时都要拉动整个源表。有没有办法使用报表服务器的登台表从生产服务器中选择数据？基本上我希望我的数据源查询看起来像这样。
SELECT * FROM ProductionDatabase.SourceTable
WHERE SourceTable.Key IN (SELECT Key FROM ReportingDatabase.StagingTable)
Thank you for looking at this. I'm new to SSIS and could definitely use some help.
谢谢你看这个。我是SSIS的新手，绝对可以使用一些帮助。
1 个解决方案
#1
0
SSIS allows you to use a Merge transformation, which should allow you to do what you want to do. That's one of the strengths of SSIS, which is that it can mix and match data from disparate data sources. So, do this as part of your SSIS data flow and don't try to do it with direct SQL.
SSIS允许您使用合并转换，这应该允许您执行您想要执行的操作。这是SSIS的优势之一，它可以混合和匹配来自不同数据源的数据。因此，将此作为SSIS数据流的一部分，不要尝试使用直接SQL。
This assumes that the key is a unique value, otherwise you might need to tweak things so that the number of rows doesn't expand, but you should be able to use the Merge then conditionally pass along rows only if a match is found.
这假设键是唯一值，否则您可能需要调整内容以使行数不会扩展，但是您应该能够使用Merge然后仅在找到匹配时有条件地传递行。
#1
0
SSIS allows you to use a Merge transformation, which should allow you to do what you want to do. That's one of the strengths of SSIS, which is that it can mix and match data from disparate data sources. So, do this as part of your SSIS data flow and don't try to do it with direct SQL.
SSIS允许您使用合并转换，这应该允许您执行您想要执行的操作。这是SSIS的优势之一，它可以混合和匹配来自不同数据源的数据。因此，将此作为SSIS数据流的一部分，不要尝试使用直接SQL。
This assumes that the key is a unique value, otherwise you might need to tweak things so that the number of rows doesn't expand, but you should be able to use the Merge then conditionally pass along rows only if a match is found.
这假设键是唯一值，否则您可能需要调整内容以使行数不会扩展，但是您应该能够使用Merge然后仅在找到匹配时有条件地传递行。