I have a table with three fields, one identifier field, and I need to add some new records from a source that has two other fields. I am using SSIS, and I think I should use the merge tool because one of the sources is not in the local database. But I am confused by the merge tool and the right process.
I have one source (Oracle table), and I get two fields: well_id and well_name with sorting after sorting by well_id. I have a destination table (sql server) and I also use this as a source. It has three fields: well_key (identifier field), well_id and release_name, and then I have sort, sort by well_id. Both are introduced into my merge task. I was going to output to a temporary table, and then somehow return new records to the sql server table.
Oracle Well SQL Well
| |
V V
Sort Source Sort Well
| |
-------> Merge* <-----------
|
V
Temp well table
I suspect that this is not the best way to use this tool. What are the right steps for such a merger?
One of my reasons for polling this method is that my merge has an error telling me that "Merge Input 2" must be sorted, but its source is the sort task, so it is sorted.
Data examples
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?