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.
InfoNote: For the Delete operation, make sure to use the destination keys, since the source keys may be null in delete cases. 

    • Related Articles

    • Devart SSIS Components Missing in Integration Services

      This article is for SSIS Data Flow Components. After installing Devart SSIS Data Flow Components, the components may not appear in Integration Services projects inside Visual Studio. This typically happens when the project configuration does not ...

    • Accessing Inserted Records Before SubmitChanges() in LINQ to SQL

      When working with LINQ to SQL and performing batch insert operations, you might need to access the properties of the newly inserted records before calling SubmitChanges(). Standard LINQ to SQL queries against the Table<T> will only reflect the data ...

    • How to update payment information for my Devart subscription

      All payment processing for our services is managed by our payment provider, 2checkout.com (Avangate Inc. acquired by Verifone). Therefore, any updates to payment details should be made through their secure account portal. To update the credit card ...

    • Working with Salesforce Attachments

      This article is for SSIS Data Flow Components for Salesforce. SSIS Data Flow Components for Salesforce can be used to download and upload Salesforce attachments. To work with documents (binary files), the ContentVersion object is required. The binary ...

    • Loading Data from Excel

      This article is for Excel Add-ins. Excel Add-ins can be used to work with external data sources and update data directly in Excel. A common question is whether it is possible to load arbitrary data from an Excel worksheet into a connected database or ...