Solved

SSIS Data Flow Lookup not matching

Posted on 2010-11-22
4
2,317 Views
Last Modified: 2012-06-22
Hi,

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.

Ian.
0
Comment
Question by:irb56
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34186848
0
 

Author Comment

by:irb56
ID: 34187082
Thanks for the prompt reply. I think I'm doing the Lookup correctly. I'm just stuck at the moment on why all my values from one table are not matching on their corresponding values in the other table. Everything goes to No Match Output when I expect it all to go to the Match Output. I'[ve tried to illustrate the problem with a screencast, creating two tables as follows:
CREATE TABLE Table1 (
MyColumn nvarchar(20) not null)
GO

CREATE TABLE Table2 (
MyColumn char(10))
GO

INSERT INTO Table1
VALUES('BPO'),('C'),('E')
GO

INSERT INTO Table2
VALUES('BPO'),('C'),('E')
GO

The data flow takes Table1 as the Source, there's a Data Conversion to convert the MyColumn from Table1 into a DT_STR(length 10, code page 1252) and then Copy of MyColumn is used to do a lookup of MyColumn on Table2. As you can see, the columns in the two tables contain identical values. However, the lookup transform finds no matches and directs everything to the No Match Output.

I hope this makes sense and you can see the issue. I think it must be something to do with data types because if I change the column data type of MyColumn in Table2 to nvarchar(20) and eliminate the (redundant) data conversion step, the data flow works as expected, pumping out all three three rows to the Match Output. I would change the data type of the column in the real world but it's made complicated by the fact this is a primary key and has multiple dependencies. I was hoping I could cope with the data type difference within the SSIS package without needing to refactor the database.

Any ideas?
irb56-372334.flv
0
 
LVL 6

Accepted Solution

by:
subhashpunia earned 500 total points
ID: 34187787
As the CHAR datatype pad the column data with " " (blank spaces) and that spaces are compared while in lookup task. So before comparing you need to remove the trim the spaces from Mycolumn data from Table2.
One more thing you should test that instead of converting the nvarchar column to DT_DTR, test with converting the CHAR column (MyColumn from Table2) to DT_WSTR (not sure what is the type for unicode string).
0
 

Author Closing Comment

by:irb56
ID: 34197300
You were spot on re: the trailing spaces. I dealt with it by loading these values into a Cache transform, applying the RTRIM function to remove the trailing spaces. I then used the cache in the subsequent lookup and all worked well.

Many thanks for your help.  :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question