Solved

SSIS Data Flow Lookup not matching

Posted on 2010-11-22
4
1,826 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
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:subhashpunia
Comment Utility
0
 

Author Comment

by:irb56
Comment Utility
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
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now