There is a source table and destination table with same structure, but in different databases and different servers . Suppose some of data from source table transferred to destination
table in past, and the ID field is not IDENTITY, so all data in
destination table has equivalent in source table. Now we want to
transfer new data from source table to destination table.
1- Suppose source table has very large amount of data, and this
is impossible to transfer all of data and then select new with NOT IN
2- source and destination is not in same sql server ,so this is impossible to left outer join .
The ID’s of tables are not linear and there is no datatime field there,
So this is impossible to fetch last inserted rows and fetch source
records from there.
- Create an OLE DB Data Source for source table, select all data from source table, remember you must write order by ID
- Create an OLE DB Data Source for destination table, select all data from destination table, remember you must write order by ID
- Go to advanced editor of each Data source and set IsSorted to
true in output columns, maybe you need to set SortKeyPosition for ID
- Add a Merge Join Transformation
- use Data Source of source table as Left, and Data source of destination table as right
- select join type as Left Outer JOIN
- select all fields of source and ID field from destination, and name the ID from destination as DestinationID
- Add a Conditional Split Task
- Create an Output name Case 1 with this condition : ISNULL(DestinationID)
- Create an OLE DB Data Destination for destination table
- Connect Case 1 from Conditional Split Task to Data Destination
The Merge Join apply left outer join on different data sources and
create List of records from source table , all ID’s from source that
has no equivalent in destination will have Null values in left outer
Then the Conditional Split Task apply an ISNULL check and select only records that has no equivalent in destination .