[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SSIS Data Flow Lookup not matching

Posted on 2010-11-22
4
Medium Priority
?
2,735 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 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