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.

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?

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.

Example data

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

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