Share via

Having difficult when transfer from ssms to ssis

Tracy 0 Reputation points
2023-04-26T14:03:34.1+00:00

[Orders_destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'orders_dimension_pk'. Cannot insert duplicate key in object 'dbo.orders_dimension'. The duplicate key value is (1, 1, 1, 1, 1).". [Orders_destination [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Orders_destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Orders_destination.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 "Orders_destination" (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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
517 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 35,556 Reputation points
    2023-04-27T01:38:11.0566667+00:00

    Hi @Tracy,

    "Violation of PRIMARY KEY constraint 'orders_dimension_pk'. Cannot insert duplicate key in object 'dbo.orders_dimension'. The duplicate key value is (1, 1, 1, 1, 1)."

    Make sure the data source does not have duplicate values on the fields that are part of the OLEDB Destination primary key. To remove duplicates that exist on the data source you can use the Sort Transformation Task (SSIS Toolbox -> Common -> Sort) and make use of the checkbox named "Remove rows with duplicate sort values". You can find a practical example here.

    You can also use the Lookup Transformation within the Data Flow task to check for data that already exist on the destination table.

    lookup-transformation-in-ssis

    Here is a same thread you may take a reference to dealing-with-violation-of-primary-key-constraint-ssis.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.