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

    • 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 ...

    • How to change the email address for my Devart account

      If you need to update your email address for your Devart account, follow the steps below: 1. Log in to your Devart account here. If you’ve forgotten your password, you can reset it using the “Forgot your Password” option on the login page. 2. Once ...

    • How to Work in Power Query Using ODBC Driver

      1. Connect to an ODBC Source In Power BI Desktop, go to the Home tab and click on Get Data to connect to an ODBC source. From the list of available connections, select the DSN connection that you created in the ODBC Administration tool. You can learn ...

    • How do I transfer my license onto a new device?

      You can install and use a Devart product on both a desktop and a laptop with a single license, as long as it is used exclusively by the same person. For more details, please refer to our EULA. If you need to transfer your license to a new device, you ...