Reputation: 1331
I am using SQL Server Express and importing via the Import Wizard so no code to offer really.
The error is shown here:
Error 0xc0202009: Data Flow Task 1: 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: "Violation of PRIMARY KEY constraint 'PK_CXS'. Cannot insert duplicate key in object 'dbo.cxs'. The duplicate key value is (00, 00, 000000, 2017, 03, 01, 00000000, 0).".
I have since done tests in Excel by filtering by the values given. Only one row comes up. I concatenated the eight columns that represent the primary key and then look for duplicates. None are found. Next, using TextFX tools in Notepad ++, I tried to remove duplicates and none were found.
Any other methods for finding this seemingly nonexistent duplicate?
Upvotes: 0
Views: 7708
Reputation: 6328
A few suggestions (that are not really a full answer, but with the given data, is as far as I can go).
In some occasions, you may have data as text (such as 1.000000001 and 1.000000002) that are different in your text files, but become the same once converted into their destination types, because of truncation, rounding, or other kind of conversion effect. This might be the reason why you cannot find duplicates in your text file, yet the database finds them.
There are few techniques you could use and check:
Apart from the imported data ... is there any previous data already on the target table? If so, there you have a possible source for duplicates.
Do you have any way to import "row by row" (or in batches) so that this can help you locate the offending one?
If none of the previous alternatives are possible, you can follow this process:
Create a table with the same structure as your target one, but without any PRIMARY KEY
or UNIQUE
constraints. Let's call it load_table
Import your data to this table. It should not complaint of PRIMARY KEY
constraints because there isn't any.
Perform the following query to find out duplicates:
SELECT
k1, k2, k3 ... kn
FROM
load_table
GROUP BY
k1, k2, k3 ... kn
HAVING
count(*) > 1
where k1
, k2
, k3
... kn
are all the columns that would comprise the primary key of your target table.
Using these techniques, you will find the duplicates that SQL Server finds but elude the methods you've used up-until-now.
Upvotes: 1