I have a table in a staging DB with a column named EmployeeClass, which is of type nvarchar(20). Within my production database I have a table named EmployeeClass with a primary key column named EmployeeClassID of type char(10). I have a data flow that is intended to check for any new distinct values of EmployeeClass in the staging database that do not already exist in the EmployeeClass table of the production DB and then insert new records into the EmployeeClass table as required.
To cater for the data type difference, I've used a data conversion transform to create Copy of EmployeeClass of type DT_STR (length 10, code page 1252). Next comes the lookup transform, which matches Copy of EmployeeClass to the EmployeeClassID column in the production EmployeeClass table. The Lookup No Matches output is directed to the EmployeeClass table to insert new values.
The problem is that 100% of the values passing through the lookup are not matching, when it should be 100% matches. I've put a data viewer on the no matches output and it shows what appear to be identical values on both sides. As a consequence of this, the data flow attempts to insert duplicate values into the destination table and fails due to the primary key constraint.
Can anyone advise why this might be happening and how best to handle it? I have a sneaky feeling that it might be to do with the production database having a different code page to 1252, but I'm not certain of this yet. If the EmployeeClassID char(10) column was associated with a different code page, would this explain the no matches?
Thanks in advance.