SSIS Error: Violation of primary key constraint when no duplicate exists 0x80040E2F

Scenario:

  1. I have a source table with dbo.Codes with primary key combination(CodeID, CodeName).
  2. In order to create my destination table, I have used the SQL Server–>Database–>Tasks–>Generate Scripts, option.
  3. Then, in the SSIS package, I simply used the OLE DB Source and OLE DB Destination element. It was returning error: “Violation of PRIMARY KEY constraint ‘pkCodes’. Cannot insert duplicate key in object ‘dbo.Codes’. The duplicate key value is (106, da7A).”.

What I have tried to solve:

  1. I have tried to use the sql command as source: Select CodeID, CodeName from dbo.Codes GROUP BY CodeID, CodeName. It was still returning error. I got confused at this point.
  2. After searching online, I found a tips to add a SSIS Toolbox–>Common–>Sort element between OLE DB Source and OLE DB Destination. I checked the option “Remove rows with duplicate sort values” in the Sort element. I was still getting error.
  3. Then, I had enabled the data viewer between OLE DB Source and Sort element. I could see there are two rows: 106, da7a and 106, da7A in the source.

What is the real problem?

My source table c0lumn CodeName is case sensitive but my CodeName column in the destination table is not case sensitive. This has occured because, sql server generate script option–>Script Collation is set to false by default.

Solution:

I recreated my destination table with Script Collation option: True, which made my destination column case sensitive and it has solved my problem.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s