SSIS Flow Pattern: Update, Insert, and Delete Records

SSIS Flow Pattern: Update, Insert, and Delete Records

This example demonstrates a basic flow pattern for updating, inserting, or deleting records when working with SSIS (SQL Server Integration Services).

In this scenario, the destination table is aligned with the source table:

 

 

The flow is:

 

 

General Flow

1. Sort Source and Destination Tables

In the Source components, configure the queries so that both source and destination tables are ordered by the key fields.

 

1. Open Advanced Editor → Input and Output Properties.
   Set IsSorted = true.

 

 

1. Define SortKeyPosition for the key columns.

 

2. Merge Join

Add a Merge Join component with the join type set to Full Outer Join. 

 

 

3. Conditional Split

Use a Conditional Split component to define rules according to the desired flow (insert, update, delete).

 

 

4. Destination Actions

Based on the split results, perform the appropriate actions:

1. Insert new records.
2. Update existing records.
3. Delete records if necessary.

Note: For the Delete operation, make sure to use the destination keys, since the source keys may be null in delete cases.