2

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 Task Factory Upsert destination component to handle the insert/update part. The source file also has a column for the update dates.

enter image description here

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Don
  • 78
  • 5
  • You want a procedure for the insert IF NOT EXISTS with an UPDATE for matched ID's. See https://stackoverflow.com/questions/11906506/sql-server-2008-if-not-exists-insert-else-update –  Feb 18 '22 at 18:40
  • Is there any transformation performed by the SSIS package? Is the Task factory component required? You can simply bulk the flat file into a stagging table and run an SQL command to upsert the data into the destination table. – Hadi Feb 18 '22 at 20:23
  • Seems you may need to add a filter task prior to your upsert and remove the rows with duplicate ID values. Perhaps send them to a file so someone can manually review them to verify that the "best" row was added/modified in the target. – SMor Feb 18 '22 at 21:59
  • @Hadi - No transformations. Pretty simple import. Just adding fields for FileName and LoadDate. I was hoping to get away from staging tables because there are close to 30 files going into 30 tables. Hoping there was a way to automatically scrub the file prior to load or use the update_date in a Merge statement. – Don Feb 18 '22 at 22:33
  • @Don do all files have the same structure? – Hadi Feb 18 '22 at 22:39
  • @Hadi - Unfortunately they are all different so I cannot use a single structure for all of them. – Don Feb 18 '22 at 22:48
  • @Kendle - That looks like it could work for a table to table import, but can it be applied to a flat file import to table? – Don Feb 18 '22 at 22:50
  • Import into a new table then do insert or update? –  Feb 19 '22 at 04:38

1 Answers1

1

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

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

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60