SSIS: Getting conflict error while importing data from source to multiple destination tables in SSIS package

Question

I am trying to import data from source table to multiple destination tables in my SSIS package.

SSIS package snippet

If I remove multicast then how to link 2 destination tables.

After removing multicast

While doing it, I am getting following error:

[AEParam [49]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK__AE__event__32E0915F". The conflict occurred in database "xx", table "dbo.AEvent", column 'eID'.".

[AEParam[49]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "AEParam.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "AEParam.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "AEParam" (49) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (62). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

[A Event 2] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_A_EVENT'. Cannot insert duplicate key in object 'dbo.AEvent'. The duplicate key value is (194).".

[A Event 2] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "A Event.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "A Event.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "A Event" (2) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

This is how I created Source tables. The destination tables also exactly the same like source tables.

    CREATE TABLE AEvent (
    eID int NOT NULL,   
    startTime datetime NOT NULL,
    endTime datetime NULL,  
    CONSTRAINT PK_A_EVENT PRIMARY KEY NONCLUSTERED (eID)
);
    
CREATE TABLE AEParam (
    eID int NOT NULL REFERENCES AEvent(eID),
    name nvarchar (446) NOT NULL,
    value nvarchar (2048) NULL,
    CONSTRAINT PK_A_E_PARAM PRIMARY KEY NONCLUSTERED (eID, name)
);

This is the View I used to get the data from the source tables.

CREATE view [dbo].[vw_Test]
AS
SELECT  AE.[eventID]  
      ,AE.[startTime]
      ,AE.[endTime]    
      ,AEP.[name]
      ,AEP.[value]
  FROM [dbo].[AEvent] AE
  INNER JOIN [dbo].[AEParam] AEP
  ON AE.eID=AEP.eID
  
GO

I am using multicast in my SSIS package to import the data into 2 different tables in the destination.

Venkataraman R Venkataraman R · Accepted Answer · 2021-06-30T04:30:11

As you are having parent child relationship, you need to INSERT parent followed by child. So, you should not do MULTICAST into parent, child tables.

Follow below steps:

First insert into AEvent
Later insert into AEParam

UPDATE

Example diagram below, where one data flow task is followed by another in the control flow.

SSIS: Getting conflict error while importing data from source to multiple destination tables in SSIS package

1 Answers