Solved

SSIS Data Flow Lookup not matching

Posted on 2010-11-22
4
1,876 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 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell and sql server - alerting 7 77
Safely Uninstall SQL Server 2008 R2 Express 3 60
Trouble viewing a table in a database. SSMS 2008 R2 4 31
TSQL previous 5 23
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

914 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

17 Experts available now in Live!

Get 1:1 Help Now