SSIS Error: Update Records with stored procedure

Question

I have a SSIS workflow where I fill a SQL Server table (260 data rows). In this SSIS workflow I have also implemented a lookup that checks whether the original row has changed. If so it should update it in the SQL Server table via a stored procedure.

This is my stored procedure:

ALTER PROCEDURE [dbo].[Update_MC_Countries] 
    @alphacode3char NVARCHAR(10),
    @alphacode NVARCHAR(10),
    @numcode NVARCHAR(10),
    @german NVARCHAR(150),
    @english NVARCHAR(150),
    @region NVARCHAR(10),
    @qmr NVARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [dbo].[MasterData_EDL_MC_Countries]
    SET [alphacode3char] = @alphacode3char,
        [alphacode] = @alphacode,
        [numcode] = @numcode,
        [german] = @german,
        [english] = @english,
        [region] = @region,
        [qmr] = @qmr

I get this error:

An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 11.0"
Hresult: 0x80040E2F
Description: "Violation of PRIMARY KEY constraint 'PK_MasterData_EDL_MC_Countries'. Cannot insert duplicate key in object 'dbo.MasterData_EDL_MC_Countries'. The duplicate key value is (DEU).".

Why does the code want to insert this row instead of updating it?

I don't get it. Any help is appreciated!

and you really don't have WHERE-condition in your update-statement ? — Sergey
This name @alphacode3char contradicts the datatype NVARCHAR(10). Your code should never lie or mislead. — SMor

Mike Cave Mike Cave · Accepted Answer · 2021-07-16T15:14:38

It's difficult to tell without seeing the dataset being updated, but perhaps you are updating a row with when there is already a row with as its primary key? I would question why you would be updating a unique key also, this would be a PK conflict. Could you show the Table Definition?

SSIS Error: Update Records with stored procedure

1 Answers