[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSIS Data Flow Lookup not matching

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

872 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