I have a SQL table with Case_id as a primary key. I'm using Visual Studio SSIS to import flat files into the table and a

If I have one record in the source file where the Case_id matches a Case_id already in the destination table the upsert works fine. But if the source file contains multiple records with the same primary key and different update dates, the package fails with a primary key violation. I don't see any functionality that allows me to select only the record with the most recent update date.

Any ideas on how I can handle that primary key violation? I'm thinking I may need to ditch the Upsert component and use some sort of MERGE statement with a MAX update date. I'm using SQL Server 2019, Visual Studio 2019, and SentryOne Task factory.

CodePudding user response：

You may need to perform a simple full load pattern or just adopt the delta load.

https://www.c-sharpcorner.com/article/handling-data-load-types-in-staging-database/ https://www.c-sharpcorner.com/article/design-the-full-load-and-delta-load-patterns-in-ssis/

Before loading your flat file to your staging table, you can SSIS Sort Transformation component :

The Sort transformation sorts input data in ascending or descending order and copies the sorted data to the transformation output. You can apply multiple sorts to an input; each sort is identified by a numeral that determines the sort order. The column with the lowest number is sorted first, the sort column with the second lowest number is sorted next, and so on