cannot insert duplicate key in object 'dbo.Testdata2'. The duplicate key value is (10107).
You got a pretty clear error message, so what are you complaining about?
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I finally got SSIS to work and loaded in Visio. I can create a table to my SqlServer with conversion errors. I'm trying to create a new column but it fails because of calculation/computation. If create a new table the data flows over . If I try to append it to the existing table the new column doesn't flow over. I notice on the csv input and I looked at the config, I notice all the columns have conversion errors. I tried changing all columns to unicode_string[DT_WSTR] and that didn't work. I tried using the suggested data type and that didn't work. I also used the conversion tool and that didn't work. Not sure what else to use.
cannot insert duplicate key in object 'dbo.Testdata2'. The duplicate key value is (10107).
You got a pretty clear error message, so what are you complaining about?
This software is horrible …one minute it works the next it doesn’t. Alteryx is way better than this. This software is full of problems you can create the same workflow one minute it works the next it doesn’t
See the workflow I created. It puts the data into the database sometimes but I have a bunch of errors. This is going into an empty table.
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [136]: 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 'PK__Testdata__9F66DFD322BCC41B'. Cannot insert duplicate key in object 'dbo.Testdata2'. The duplicate key value is (10107).".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination [136]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB 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.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (136) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (149). 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.
Hi @John Burks ,
It's clear that you are inserting duplicate key into your table and then thrown the error.
Which is why > one minute it works the next it doesn’t.
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 use the Lookup Transformation within the Data Flow task to check for data that already exist on the destination table.
Regards,
Zoe
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
That’s the problem the table is empty all I’m doing is moving the csv data from the file to an empty table.What I’m not supposed to bring in the key data. What would be the point of bring in the data without the keys. Again the data is going into an empty table. I do it with Alteryx with no issues. I can use Python and it has no issues.Even when it creates a new table it ssis throws errors.