Solved

SSIS Lookup transformation with ignore case

Posted on 2008-10-14
4
1,028 Views
Last Modified: 2013-11-30
Hi experts,

I use the lookup task in SSIS. But the task sarches for exact matches.
I need to make a lookup which ignores cases.
So I tried to change the generated SQL-statement using the modify box in the advanced tab.
I tried to add the LOWER() function to the statement but an error was returned

Any suggestions?
select * from
	(SELECT a.name, k.PLZ,a.vorname,  k.name AS kundenname, k.Nummer , kg.SalesPersonCode
FROM kansprechp AS a JOIN Kunden AS k ON a.KundenCode = k.Code JOIN KundenGr AS kg on k.GrCode = kg.GrCode) as refTable
where LOWER([refTable].[name]) = LOWER(?) and [refTable].[PLZ] = ?

Open in new window

0
Comment
Question by:arthrex
[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 17

Expert Comment

by:HoggZilla
ID: 22715195
There is nothing wrong with your Syntax, the Lower function will work as you have written. What is the exact error. Are you selecting against a MSSQL db? Do your variable datatypes match the columns in the where clause?
0
 

Author Comment

by:arthrex
ID: 22718543
Thanks for your reply.

The datatypes have to match, because without the Lower() function it works.
Attached you see the exact statement and the error messages.

Thanks
sqlStatement.GIF
errormessage.GIF
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22719501
I suggest you delete the lookup component and create a new one, I have been suprised many times to find this as a solution.

Next, add the LOWER for a.name to the SQL on the Reference Table tab. This might fix it for you, if not add create another column in your source query that is a lower case for your paramenter.
0
 

Author Comment

by:arthrex
ID: 22720874
Renewing the shape didn't work. SSIS doesn't seem to support the lower() function for the parameters in the statement (lower(?))
So adding a new column solved the problem. Thanks for the workaround.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

635 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