在SSIS中使用合并任务的准则 [英] Guidelines for using Merge task in SSIS

问题描述

我有一个包含三个字段的表，一个是身份字段，我需要从具有其他两个字段的源中添加一些新记录.我正在使用SSIS，我认为我应该使用合并工具，因为其中一个来源不在本地数据库中.但是，我对合并工具和适当的过程感到困惑.

I have a table with three fields, one an identity field, and I need to add some new records from a source that has the other two fields. I'm using SSIS, and I think I should use the merge tool, because one of the sources is not in the local database. But, I'm confused by the merge tool and the proper process.

我有一个源(一个Oracle表)，我得到两个字段well_id和well_name，后跟一个排序，按well_id排序.我有目标表(sql服务器)，并且我还将其用作源.它具有三个字段:well_key(身份字段)，well_id和well_name，然后我有一个排序任务，对well_id进行排序.这些都输入到我的合并任务中.我将输出到一个临时表，然后以某种方式将新记录返回到sql server表中.

I have my one source (an Oracle table), and I get two fields, well_id and well_name, with a sort after, sorting by well_id. I have the destination table (sql server), and I'm also using that as a source. It has three fields: well_key (identity field), well_id, and well_name, and I then have a sort task, sorting on well_id. Both of those are input to my merge task. I was going to output to a temporary table, and then somehow get the new records back into the sql server table.

Oracle Well                     SQL Well
     |                           |
     V                           V   
Sort Source                     Sort Well
     |                           |
     ------->  Merge* <-----------
                |
                V
              Temp well table

但是，我怀疑这不是使用此工具的最佳方法.这样的合并的正确步骤是什么?

I suspect this isn't the best way to use this tool, however. What are the proper steps for a merge like this?

我质疑这种方法的原因之一是我的合并有一个错误，告诉我必须对合并输入2"进行排序，但是其来源是一个排序任务，因此需要对其进行排序.

One of my reasons for questioning this method is that my merge has an error, telling me that the "Merge Input 2" must be sorted, but its source is a sort task, so it IS sorted.

示例数据

SQL Well (before merge)
well_key   well_id   well_name
1          123       well k
2          292       well c
3          344       well t
5          439       well d

Oracle Well
well_id     well_name
123         well k
292         well c
311         well y
344         well t
439         well d
532         well j

SQL Well (after merge)
well_key   well_id   well_name
1          123       well k
2          292       well c
3          344       well t
5          439       well d
6          311       well y
7          532       well j

将Oracle Well加载到临时本地文件，然后在其上使用sql插入语句会更好吗?

Would it be better to load my Oracle Well to a temporary local file, and then just use a sql insert statment on it?

推荐答案

首先，我强烈建议在合并之前摆脱排序转换.排序是非常昂贵的阻塞异步转换，可以通过执行以下命令来轻松避免:将输出列的ole db src advance属性更改为issorted = true并将该列的sortkeyposition属性更改为1.不要忘记添加您的由于ole db源不会自动为您执行此操作，因此请在您的tsql中进行排序:

First of all, I would highly recommend getting rid of the sort transformation before merging. The sort is very expensive blocking asynchronous transformation and can be easily avoided by doing an order by and changing your ole db src advance properties of your ouput columns to issorted = true and changing the sortkeyposition property of that column to 1. Dont forget to add your Order by in your tsql since the ole db source does not automatically do this for you:

同步vs异步

Oledb Src已排序财产

然后，我将使用合并"测试pkg的性能.看起来您要拉的字段的大小没有那么大；但是，如果有上千万个记录，那么我还建议创建一个pkg，将数据放入两个登台表中，将它们连接起来，然后按照上面建议的那样插入数据，以查看使用以下方法是否可以获得更好的性能这种方法.

Then, I would test the performance of the pkg using the Merge. Looks as though the fields you are pulling are not that big in size; however, if there is 10's of millions of records, then I would also recommend creating a pkg that lands the data into two staging tables, joining them, and then inserting the data as you have suggested trying above to see if you get better performance using that approach.

希望这会有所帮助.

这篇关于在SSIS中使用合并任务的准则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！