I just had to do this for the first time myself and had a little trouble but got it working this morning. Here's a high level:
I'm assuming you have a situation where, as a simple example, source has 10 records, destination has 7. You need to insert the 3 records from the source that do not exist in the destination.
- Use a Data Flow Task
- Edit the Data Flow Task
- Add an OLE DB Source connection for your source server and configure accordingly (select the connection manager and the table)
- Add a Lookup task. Connect your OLE DB Source connection to the Lookup task. Edit the Lookup task and on the "General" tab, select "Redirect rows with no match to output" in the drop down. On the "Connection" tab, select your destination server and corresponding table you need to insert into. On the "Columns" tab, click and drag the PK from the Available Input Columns to the corresponding PK in the Available Lookup Columns. This is what it will use to determine if there are new records. (In my task, I did not check any of the checkboxes in the Available Lookup Columns.)
- Add an OLE DB Destination connection for your destination server. Connect the Lookup task to the OLE DB Destination. In the "Input Output Selection" popup, select "Lookup no Match Output" in the "Output" drop down.
- Edit the OLE DB Destination and configure for your destination server and table. Make sure to select the Mappings tab to get the columns to map.
I'm definitely no expert when it comes to SSIS, but was able to get this working. If I still ran into trouble, I was going to go to that Stairway series Steve referenced. 🙂