SSIS tutorial: How to insert only new rows in SQL Server

[daniel]

Introduction

In this article, you will learn how to insert new rows into a destination table using the Upsert destination with a source component. This approach allows you to insert only new records while preventing updates to existing rows, making it ideal for incremental load scenarios.

Prerequisites

• Download and install the ZappySys SSIS PowerPack.

Steps

Configure the JSON Source component

1. Drag and drop the JSON Source component into the Data Flow. In this example, we will use the JSON Source.
   

   

2. Configure the source component. In our case, we will select Example1 as the data source.

3. Click Preview to validate the data.

4. Click OK to save the configuration.
   

   

Configure the Upsert Destination for insert only

1. Drag and drop the Upsert Destination component into the Data Flow.

2. Connect the JSON Source to the Upsert Destination.
   

   

3. Double-click the component and create or select a connection to the destination database.

4. Select the destination table and click preview to check the data. In this table, we have 3 rows, and in the final result, we will insert the fourth row.
   

   

5. Enable only the Insert option.

6. Map the source columns to the destination columns.

7. Select the primary key column used to identify new rows.

8. Click OK to save the configuration.
   

   

9. Drag and drop a destination component (for example, Trash destination).

10. Connect it to the Upsert destination output to review the inserted rows.

Execute the package and review the results

1. Run the SSIS package.
2. Verify that only new rows are inserted into the destination table.
3. Confirm that no existing rows are updated.
   
   

Conclusion

Using the Upsert destination in insert-only mode is an effective way to load new records into a destination table without modifying existing data. This configuration is commonly used in incremental data loads and helps maintain data integrity in ETL workflows.

Explore our SSIS PowerPack for more information and download and install it to start building smarter, faster, and more scalable solutions.

References

• Upsert Destination
• Upsert Destination documentation
• Blog articles
• SSIS PowerPack

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.