SSIS查找转换替代方案? [英] SSIS lookup transform alternatives?

问题描述

我每天需要将大约 1100 万行数据从一个数据库传输到另一个数据库.此时源表总共大约有 50 亿行.

I need to transfer about 11 million rows daily from one database to another. The source table is about a half a billion rows total at this point.

我使用的是从 开始获取所有内容?"方法，使用目标中的最大值作为 ?，但源的维护有点时髦.他们不断回去填补漏洞，而我的方法不起作用.

I was using the "get everything since ?" method, using the max in the destination as the ?, but the maintenance of the source is kind of funky. They keep going back to fill holes and my method isn't working.

标准查找转换需要数小时才能运行.Pragmatic 的 TaskFactory 有一个 Upsert 组件，但不在这个项目的预算之内.

The standard Lookup transform takes hours to run. Pragmatic's TaskFactory has an Upsert component, but it's not in this project's budget.

有没有比 Lookup 更好的查找方式?

Is there a better way than Lookup to lookup?

推荐答案

这里有一些选项:

A.通过实施某种 CDC 来减少输入数据(在您所说的数量和数据可变性方面，您应该真正考虑这一点).对于源头的 CDC，您有哪些选择(即，您可以创建触发器和日志记录表吗?您是否有支持本机 CDC 的 SQL Server 版本?)

A. Reduce the input data by implementing some kind of CDC (at the volumes and data variability you're talking you should really consider this). What options do you have for CDC at the source (i.e. can you create triggers and logging tables? Do you have a version of SQL Server that supports native CDC?)

B.将输入数据加载到临时表中并使用 INSERT/UPDATE 或 MERGE 将其应用到目标表

B. Load the input data into a staging table and use INSERT/UPDATE or MERGE to apply it to your target table

C.将输入数据加载到临时表中，然后删除/插入(基于日期范围)以将其应用到目标表.这是我一般的做法.您的加载过程应该能够在给定的日期范围内运行并智能地仅加载该数据，将其从目标中删除并重新加载.

C. Load the input data into a staging table and DELETE/INSERT (based on date ranges) to apply it to your target table. This is what I generally do. Your load process should be able to run off a given date range and intelligently load only that data, delete it from the target and reload it.

恕我直言，SSIS 查找组件对您所说的数据量没有用.

IMHO, the SSIS lookup component is of no use at the data volumes you're talking.

这篇关于SSIS查找转换替代方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！