SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds
I had the O.P. check for duplicates in the destination table. The following query was used:

SELECT ITEM_GROUP, ITEM, COUNT(*)
FROM Lawson.dbo.ITEMMAST
GROUP BY ITEM_GROUP, ITEM
HAVING COUNT(*) &gt; 1;


The result was:


  0x2020202020202020202020202020202020202020202020202020202020202020


The row starting with 7 "nul" characters (i.e. CHAR(0) or 0x00, see image #2 in the question) was replaced with a row of all spaces.

The problem is that the first character is 0x00, which is the null terminator for strings. Pulling that value into SSIS (i.e. .NET) probably viewed it as a simple empty string. And inserting an empty string into a CHAR() column (i.e. a blank-padded datatype) will naturally leave you with all spaces. Hence your duplicate row.

At this point, I would say that it's simply bad data, unless you feel a need to preserve an invalid string (no string should ever have anything after a 0x00, nor should it really even have the 0x00, but if it does, it would be the last character).

So, you can correct that row at the source, but it can't be an empty string since that would get the same PK violation that you are running into now. Is there a default dummy value?

Or, if that row is truly invalid / not used, then I would remove it, OR maybe find out how that row got there in the first place. Did someone manually enter it, did the app create it? Was it a botched import? You might take this opportunity to add a CHECK constraint to ensure that no row has the 0x00 character in it in the future (this would help identify the culprit in the future). Then delete the row from QA and move on... :)

I suppose you can always try to exclude that row if need be, but it would be best to prevent it from ever existing. It will never appear correctly when selected. And there might be other negative impacts downstream in the future. How much time have you (and I ;) spent on finding / fixing this? Do you really want to do it again, or have someone else hunt this down? If that row could never show up in prod, then it should be removed from QA.