SSIS - Nightly import package that updates and inserts records

This walkthrough builds an SSIS package for a nightly import. Records already imported are updated, and new records are created in the destination.

For the demo, assume a source system (say a CRM) with a view like this:

The job imports this data into an Employee table in the destination:

Steps

1. Open SQL Business Intelligence Studio and create a new "Integration Services Project".

2. From the toolbox, drop a Data Flow Task onto the control flow.

3. Double click it and add an OLE DB Source.

4. Double click the OLE DB Source.

5. Click New to create a connection and enter the source database details. Select the connection and set the other properties.

6. Add a Lookup transformation. It tells us whether the record already exists in the destination.

7. Add a connection to the destination first.

8. Set up the Lookup transformation.

9. By default, a Lookup fails when no match is found. That is exactly the case for new records, so set the Error Output to let them through the pipeline.

10. Add a Conditional Split.

11. Drag the data flow arrow into the Conditional Split. In the dialog, select "Lookup Match Output".

12. Double click the Conditional Split and define the outputs.

13. Add an OLE DB Destination and an OLE DB Command. Connect the New_Records output to the destination, and the Modified_Records output to the command.
14. Configure the OLE DB Destination.

15. Configure the OLE DB Command.

16. Press F5 to run and test the package.

The package is ready. Scheduling it to run every night is the next step.